2 Form Questions for the Group...

[Guest]

Question 1:

Is there a command that will allow me (in queries for example) to match the
conditions of one field to that of multiple fields or values? For example,
in another data analysis tool I have used in the past, I could use
Match([Primary Search Field], [Condition 1], [Condition 2], [Condition 3]).
In other words, using this command, I could eliminate the need for multiple
"iif" commands. Anyone know the function?

Question 2:
I have a form with list boxes that I am using to select filter criteria from
before running a query. Within the query, I am using these commands:

[Forms]![Form1]![Employee Name] Or [Forms]![Form1]![Employee Name] Is Null

When I change the list boxes on the form to allow to me select multiple
filter criteria (such as two employee names), it will bring back all of the
records. How can I modify the command to filter (within the query) on the
two employees selected in the form?

Thanks - and to the regular posters, you guys are great! I have learned a
ton from each of you.

Peter Leman

 

[Allen Browne]

Q1.
Not really clear, but I think you are looking for the IN operator, where one
field is matches against several values.

In the Criteria row under [Primary Search Field], you would enter:
IN ("some value", "another value", "third value")
or for a numeric field:
IN (3,6,7)

Other alternatives might be Switch() or Partition(), but I don't think they
do what you want.

Q2.
You cannot use a multi-select list box directly as the criteria in a query,
because the Expression Service will not parse the reference, and visit each
entry in the ItemsSelected.

You can programmatically create the string by looping through the
ItemsSelected collection. Once you have the WHERE clause, you can then
complete the SQL string, and assign it to the form's RecordSource. This can
be *much* more efficient than lots of " OR xxx Is Null" phrases in the WHERE
clause.

For an example of building the WHERE clause from a multi-select list box,
see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html