parameter query to find field entries in a list

[Mary]

In Access XP, I am trying to create a parameter query to
find a grouping of records which have criteria from a list
of possibilities. For example, in a STATE field, I might
want to have only the entries from NY, NJ and CAL for my
result. In the criteria area, I have tried typing "In
([Enter desired states, separated by commas])". When this
executes, I get no results, because it returns only the
records where NY,NJ and CAL are ALL entered in the field.
What am I doing wrong? How do I get the desired result?

 

[Tom Ellison]

Dear Mary:

It is a common misconception that you can recreate the functionality of an
IN clause using a single input parameter. That is a blind alley.

The best way to do this is to create a multi-select list box on a form in
which you can list all the states and the user can select any combination he
wishes. You would probably trigger the opening of your query with a command
button. At this time you would generate the SQL string dynamically from
inputs on the form such as this list box and cause it to be run. You must
code in VBA to do this.

A much less satisfactory method is to us INSTR() to perform what you want.
Since your state abbreviations will not contain a comma within them, a comma
can be used to separate them as before. If you append a comma at the
beginning of the user supplied list of state abbreviations, and another
comma at the end, then any single state from the rows in the database can be
tested with INSTR("," & [Enter list of state abbreviations:] & ",", "," &
[Your state field] & ",") > 0. That is to say, if the user enters:

AA,BB,CC

this will search:

,AA,BB,CC,

for any state in the database, such as CC, by looking for:

,CC,

This is the "poor man's" approach that avoids coding VBA to get the
solution.

Note that the user must refrain from typing any spaces whatsoever or it will
fail. That is, especially, no spaces after the commas in the list.