2 Questions; 1 Frustrated User...Form Filter & Query

[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

 

[MacDermott]

To Question 1:
If you have only one field you're applying criteria to, in the QBG you
can put as many criteria as you like (well, up to about 10) on the lines
below that field; this creates "OR" conditions.

To Question 2:
A multiselect listbox does not return a single value; you have to write
code to look at each line and see whether it's selected. You can probably
get some ideas in the Access Help file under multiselect listbox.

 

[Joseph Meehan]

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?

BEEP BEEP BEEP

This sounds like a poorly designed table. It sounds like you should
have the conditions in their own table related to the primary table. Then
you would not be asking the question.