By Alison Barrows, Joseph C. Taylor If you want to filter multiple fields in Access , the flexible Filter by Form feature is what you need. Although you can apply the filters to the various fields by using several different techniques, the Filter by Form feature lets you define all your filters for the table at the same time and then see the results.
You can also select multiple criteria for a single field by using the Filter by Form feature. Use this form to specify the criteria you want to use to filter your data. Filtering by form in Access When you filter by form, you can use multiple criteria, and you also get to choose how the data filters through the criteria you set up. Do you want a record to meet all the criteria before it shows up onscreen, for example, or is meeting just one criterion enough to display the record in the filtered datasheet?
Use the following two operators to tell your criteria how they should act together: The criteria act together hand in glove; a record has to pass all criteria to display in the filtered datasheet.
A record has to pass only one criterion to display in the filtered datasheet. You may use more than two criteria with the Or and And operators. The way that you put criteria in the form defines how multiple criteria act together.
Use the Look For and Or tabs at the bottom of the form, as follows: To take advantage of all this versatility, follow these steps to filter a datasheet by form: Access displays the Filter by Form window, which looks like an empty datasheet. Move the cursor to a field for which you have a criterion. If you want to see only addresses in Pennsylvania, for example, move the cursor to the State field.
A down arrow appears in the field. Click the down arrow to see the entries in the field. You may want to type the first letter or digit of your criterion to move to that point in the drop-down menu. Choose the value that you want the filtered records to match.
Access displays the text that the filter is looking for inside quotation marks. The asterisks are wildcards that stand for anything else that may appear in the cell. If you have a criterion for another field that needs to be applied at the same time as the criterion you set in Step 4, repeat Steps 2—4 for the additional field. Setting up criteria to work together illustrates the usefulness of the And operator.
If you have a completely different set of rules to filter records by, click the Or tab in the bottom-left section of the Filter by Form window. Access displays a blank Filter by Form tab. When you set criteria on more than one tab, a record has to meet all the criteria on only one tab to appear in the filtered datasheet.
Create the criteria on the second tab in the same way that you created those on the first — that is, click the field, and choose the value that you want to match. If, in addition to all the addresses in San Francisco, you want to see all the addresses in Boston, set the State field on the Or tab to MA and the City field to Boston.
When you use an Or tab, another Or tab appears, allowing you to add as many sets of Or criteria as you need.