Text String from Form Query - Multiple Choices

[wrandyrice]

Been a while since I put hands on access. Working with a very simple database.



Working with 1 table, 1 form, 1 query.

Query has several fields. Field of interest contains names of counties.

In query design view I can type, "CountyA" Or "CountyB" or "CountyC" in the criteria and the desired results are returned.

On form I've created a text box and an open report button. The report I'm trying to open uses the query as the data source. I reference the text box on the form as the criteria for the query as follows:
[Forms]![frm_LMIBasicReport]![txtCounties][Forms]![frm_LMIBasicReport]![txtCounties]

If I enter the the name of a single county into the form it executes perfectly. If I try to enter multiple counties it fails. This is true whether I just enter the county names or replicate the exact criteria string I use in the source query. For example
CountyA works and returns desired values for County A
CountyA Or CountyB or CountyC fails. The report opens but no records are returned
"CountyA" Or "CountyB" or "CountyC" = exactly how it is entered in the query if I'm not using the form opens report but no records are returned

Please advise, how can I pass multiple values from a single text box to the query.

Thank you,

wrandyrice

[John_G]

Hi -

While you can type "CountyA" Or "CountyB" or "CountyC" into the criteria in query design view, that is not the SQL it generates. If you look at the query in SQL view, you will see that the SQL looks something like : (field1 = "CountyA") Or (field1 = "CountyB") or (field1 = "CountyC")

There is an easy other way to do what you need to do.

First, remove the reference to the form field from the query criteria.

Then, in your form, put the required criteria into a string variable so that it looks like a WHERE clause, but without the "where", using the proper syntax as I pointed out above.

Then open the report with the Docmd.openreport, with the string variabe (criteria) as the 4th parameter:

Dim SQL as string
SQL = "Field1 = 'County a' or field1 = 'County B' or Field1 = 'County C' "
Docmd.OpenReport ReportName,,,SQL

You will need some VBA to create the proper string format if the user enters "A" or "B" or "C"

John