Query Criteria causing a headache

[GavinBlackburn]

Hi,

I'm having a problem with some query criteria and I'm not sure why it itsn't working.

I have a form with several drop boxes that allow the user to select a Chemical element symbol (C, H, N, O etc.). Next to these boxes I have a text box that allows them to enter a number. The idea is they can use these to build up a chemical formula (C3H7NO2) and then search this against a column in a table that contains chemical formulas and return the ones that match the formula entered.

I have tried the following code Like "*" & [Forms]![SearchF]![ElementOne]&"*"&[Forms]![SearchF]![ElementNumberOne]&"*" etc. but it doesn't work.



Can anyone help with this?


It would also be good if the letters and number could be in any order in the dropdown boxes, i.e. C6H2N2O2 would return C6H2N2O2 and C6N2H2O2 etc.

Is this possible?

Many thanks,

Gavin.

[Rod]

I think your problem is that you are missing quotes in the resultant string. There's also possibly an unwanted asterisk.

The syntax when typed from scratch is:

"*C6*"

Your string concatenation will result in:

*C*6*

So try:

"""*" & [Forms]![SearchF]![ElementOne] & [Forms]![SearchF]![ElementNumberOne] & "*"""

[GavinBlackburn]

Hi,

Thanks Rod, I found that it was referencing the ID in the second table rather than the Elements so was only searching for numbers. Got it sorted now,

Cheers,

Gavin.