Filtering Multiple Fields
To give you an understanding of the scenario for Filtering
Multiple Fields; you may have a situation where you have 3 fields on a form, and
you want the user to be able to enter data into one or more of these fields. If
the field was left blank, then you would want all of the values in this field
included in your result. The underlying query would then be filtered according
to the data entered into the fields on the form.
For this example to make more sense to you, I have included a
library scenario as follows:
You have three fields you wish to be able to filter by:
Type of book (novel, reference, etc).
You may wish to query only those written by a certain author, but
not by anything else, or you may wish to query all Novels by a certain
Publishing Company, but to include all authors.
You can set this up by utilizing an unbound form to set the
criteria for the underlying query.
To do this, create a new unbound form; add an unbound combo box
for each of the criteria you wish to set. You could also use a text box, but a
combo box helps to prevent user error, i.e. the combo boxes would display the
values already entered into the database and the user would only have to select
one. If a text box was used this could cause problems in two areas:
1) There is no prompt for the user as to what to enter.
2) Prevents spelling mistakes on incorrect information.
For the above example you would add three combo boxes. One for the
author, one for the publishing company and one for the type of book. Do this by
following the combo box wizard, choosing to look up the value in a table or
query. Choosing the table or query that has the relevant list of values.
You may wish to build queries first, especially if you are likely
to have duplicates in your tables. Build one query for each field, and only have
that field in the query. Choose to have no duplicates. To do this, with the
query in Design view, right click in a blank section of the query and select
properties from the drop down list. Then change the property Unique Values
from No to Yes. This will prevent duplicates.
You can then base your combo boxes on these queries.
When finished building the queries and the combo boxes, build the
query which contains the fields you wish to set the parameters for, and any
other relevant fields.
Now, using the above example in the Author column in the criteria
row type the following:
Like "*" & [forms]![NameOfForm]![NameOfComboBox] & "*"
Changing NameOfForm to the name of your form and NameOfComboBox to
the name of the combo box that matches the corresponding combo box for that
Repeat for the other fields, keeping them all on the same row in
the criteria section.
Note: This will only work for fields that are of text (string)
The * is a wild card, therefore if there is no value entered into
this field in the form, the resulting criteria would be **, which would display
all records for this field.