Multiple variables in MS Access form field

On a Microsoft Access form field I can enter a PO number (ex. 123) that is looked up by a query with this code [Forms]![dates].[txtPOs] and it works fine but only if I enter a single PO number.

However if I enter multiple PO numbers in the form field it will never work, even if I enter the POs as "123 Or 124".  If I go directly into the query and paste "123 Or 124" in the PO field (in design view) it will work.
steve lemmonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Arguments in queries accept only DATA.  The "OR" is a relational operator and is therefore part of the structure of a query.  If you want to create complex clauses, you must build the query on the fly using VBA.  So you would have a fixed string and a variable string that you concatenate.

strSQL = "Select * From YourTAble WHERE "

Then you can concatenate whatever where clause you want.  REMEMBER, you always have to repeat the field name so

fldA = 1 or 2 ------- is NOT VALID
fldA = 1 or fldA = 2   Is VALID

Or, you can make an In() clause

FldA IN(1, 2)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
steve lemmonAuthor Commented:
I didn't quite get your first option.  I'm not using a table to get the PO numbers, I'm using a form field.

I tried your second option using
WHERE PO.PO_No IN ([Forms]![dates].[txtPOs])
and it works if I only enter one PO number in the form field but if I enter more than one PO number then the query gives me no results.  I tried entering two numbers in the form field in many ways as shown below using all sorts of punctuation but to no avail:
51250 51355
51250, 51355
"51250" "51355"
'51250' '51355'
"51250", "51355"
'51250', '51355'
"51250" Or "51355"
'51250' Or '51355'
PatHartmanCommented:
I'm not using a table to get the PO numbers, I
I didn't say you were.
Only DATA can be passed as an argument.  The quotes and commas separating them are NOT DATA.  You must build the WHOLE query using VBA.

Access is seeing your form field reference as:

"51250, 51355"

rather than

51250, 51355

Because it is expecting the argument to contain ONE AND ONLY one piece of data.

Look at the third example.  It builds a query and then uses it as the RecordSource for the form.
FillFormFields.zip
PatHartmanCommented:
answer provided
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.