Not all that complex query, possible data type error?

[kballing]

I have the following query

SELECT items.itemKey, item.sample AS [Sample ID], item.PK AS EMPI, item.account AS Account
FROM items
WHERE (((items.sample) Like "*" & [forms]![tubetrack].[SampleID] & "*")) OR (((items.PK)=[forms]![tubetrack].[EMPI])) OR (((items.account)=[forms]![tubetrack].[AccountNo]));

I get an error:

This expression is typed incorrectly, or it is too complex to be evaluated. . .

I have three text boxes in my form and I want to be able to search by any of them. Item.PK and item.account are numbers and items.sample is a string. The results of the query are linked to a list box and I have some background code involved, but the problem seems to be in the query.

Is it just too complex to have three criteria like this?

 

[RuralGuy]

I would try:

SELECT itemKey, sample AS [Sample ID], PK AS EMPI, account AS Account
FROM items
WHERE (((sample) Like "*" & [forms]![tubetrack].[SampleID] & "*") OR ((PK)=[forms]![tubetrack].[EMPI]) OR ((account)=[forms]![tubetrack].[AccountNo]));

 

[kballing]

That doesn't work. This original code is simplified (still gives the error though) and once I figure it out it will have a few more fields and a join.

 

[RuralGuy]

Have you tried using the query builder to help? Putting criteria in the same line denotes AND and on different lines denotes OR. Start with only one criteria and then work up to the three.

 

[MSAccessRookie]

I have the following query

SELECT items.itemKey, item.sample AS [Sample ID], item.PK AS EMPI, item.account AS Account
FROM items
WHERE (((items.sample) Like "*" & [forms]![tubetrack].[SampleID] & "*")) OR (((items.PK)=[forms]![tubetrack].[EMPI])) OR (((items.account)=[forms]![tubetrack].[AccountNo]));

I believe that this code will simplify to the following:

[FONT=Courier New][B]SELECT items.itemKey, item.sample AS [Sample ID], item.PK AS EMPI, item.account AS Account[/B][/FONT]
[FONT=Courier New][B]FROM items[/B][/FONT]
[FONT=Courier New][B]WHERE [/B][/FONT]
[FONT=Courier New][B][COLOR=purple][COLOR=red]( ( [/COLOR]([/COLOR] [COLOR=teal]([/COLOR] [COLOR=blue]([/COLOR] items.sample  [COLOR=blue])[/COLOR] Like "*" & [forms]![tubetrack].[SampleID] & "*" [COLOR=teal])[/COLOR] [COLOR=purple])[/COLOR]   OR[/B][/FONT]
[FONT=Courier New][B][COLOR=purple] ([/COLOR] [COLOR=teal]([/COLOR] [COLOR=blue]([/COLOR] items.PK      [COLOR=blue])[/COLOR] = [forms]![tubetrack].[EMPI]                    [COLOR=teal])[/COLOR] [COLOR=purple]) [/COLOR][COLOR=red])[/COLOR] OR[/B][/FONT]
[FONT=Courier New][B][COLOR=purple] ([/COLOR] [COLOR=teal]([/COLOR] [COLOR=blue]([/COLOR] items.account [COLOR=blue])[/COLOR] = [forms]![tubetrack].[AccountNo]               [COLOR=green])[/COLOR] [COLOR=purple])[/COLOR] [COLOR=red])[/COLOR] ;[/B][/FONT]

As you can see, the Blue, Green, and Violet groupings are complete. However, sometimes Access has problems with grouping compound SQL statements unless you help it. Try adding the Red ones (or some similar set) to direct Access on the order of processing for the query.

NOTE: These changes would be made in SQL View. I am not sure how to tell you to make them in Design View. I await responses so that I can learn something new.

 

[kballing]

I figured it out. The error was coming from the fact that the criteria for the numeric fields was "items.PK = ..." and should have used the LIKE expression, i.e. "items.PK like ..."

Also I found it neccesary to change the first criterion to also have "(... AND forms.tubetrack.SampleID Is Not Null )" so that if the field was blank, it didn't return all records.

 

[RuralGuy]

There you go. Off and rolling again.

 

[Brianwarnock]

Are you saying that your fierst criterion reads

((sample) Like "*" & [forms]![tubetrack].[SampleID] & "*" AND forms.tubetrack.SampleID Is Not Null )

Does that make sense?

Reading through I thought that the answer was going to be that you would have to add Or Forms! etc Is Null to each criterion and change the linking Ors to Ands .

This allows you to ignore any particular criterion.

Brian

 

[kballing]

For the SampleID criteria, wildcards were involved and therefore needed to exclude nulls with an AND statement.

For the other two fields (numeric data) I had them as x = y, but should have been x like y because the input is a string and then actual field was not.

I want to be able to see the results of any combination of each input field so OR is appropriate. There aren't any nulls in any of these fields, although possible, so I'm not worried about returning tons of extra records.

I know I've been a little sloppy and confusing, but I now have what I want and it works well. Suprising how posting to a forum helps you solve your own problem.

 

[RuralGuy]

We are the sounding wall you needed.