How to reference form field with AND or OR criteria

[Autoeng]

I have a query that returns results based on PartNumber. The query PartNumber field criteria is referencing a form field. Works great when a single part number is entered on the form but when you try to enter 2 part numbers separated by AND or OR it returns no records. It does work if this is the criteria you input directly into the query instead of using the form field reference. I even tried enclosing the PartNumbers in the form field with quotations (i.e. "10001" AND "10002") but that didn't work either.

Is there a way to enter multiple PartNumbers in the form field and the query return the correct results?

 

[Rich]

Any help?
Article ID: Q100131

 

[Autoeng]

Thanks Rich,

Can't use the AND criteria using that but it will suffice until someone comes up with something better.

 

[Autoeng]

Oops. Spoke too soon. Referenced my form field but query now displays the box from the InStr query field instead of looking at the form field.

 

[Autoeng]

Does anyone else have a possible solution to this? The MS article uses a system parameter box when I want to use a form field so it's not working out for me.

 

[stevehooley]

what about?....

What about creating a table (rather than a single field) where you can input the part numbers for the criteria? Then you can query this with the table you want the results from.

 

[Autoeng]

I don't understand what you mean by entering the part numbers I am looking for into a table and using this as the criteria for the query. Can you point me to an example of this somewhere or what it is called so I can look up some more info on it?

 

[stevehooley]

i hope this helps

is this ok? the pic shows the query builder screen in design view.

this will give you a query showing all the records in the RESULTS_TABLE that match with any parts in the ENTER_PARTNOS table

 

[Autoeng]

Perfect. I wasn't thinking obviously.