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:

Author;

Publishing Company;

and 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 particular field.

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) data type.

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.