How to Filter Multiple Fields in Access 2013
If you want to filter multiple fields in Access 2013, 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.
To filter by form, click the Advanced button in the Sort & Filter group on the Home tab of the Ribbon, and choose Filter by Form from the drop-down menu. Access displays a form that looks like a single row of the table you’re filtering. Use this form to specify the criteria you want to use to filter your data.
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:
And: The criteria act together hand in glove; a record has to pass all criteria to display in the filtered datasheet.
Or: 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:
Criteria on a single tab act as though they’re joined by the And operator.
Criteria on separate tabs act as though they’re joined by the Or operator.
To take advantage of all this versatility, follow these steps to filter a datasheet by form:
Click the Advanced button in the Sort & Filter group on the Home tab of the Ribbon, and choose Filter by Form from the drop-down menu.
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.
If you aren’t seeking to match the entire field but are looking for a match in part of the field, type LIKE “*value that you’re looking for*” (including the quotation marks). You’d type LIKE “*new*” in the City field to find all records with new in the city name, for example. 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 want to find addresses in San Francisco, set the State field to CA and the City field to San Francisco.
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.
Click the Filter button in the Sort & Filter group on the Home tab of the Ribbon to see the filtered table.