Creating and Applying Filters
When you use criteria in a query, Access displays a subset of records in the underlying data source. To revise the selection criteria, you have to open the query in Design view and enter one or more new expressions in the Criteria row of the design grid. You then have the option of saving these new criteria as part of the permanent design of your query.
A filter is a faster, more convenient way to temporarily focus on specific records in a query, table, or form. You can develop and apply filters quickly, without switching to Design view, and return to the unfiltered display whenever you want to see the entire set of records again.
The easiest way to create a filter is to base it on the contents of an existing record. When a query or table is open in Datasheet view or in a form, right-click and use the Filter by Selection or Filter Excluding Selection options (these options are also available via toolbar buttons or from the Records, Filter menu). When you choose one of these options, Access shows or hides records based on your selection. The exact filter action depends on which of the following three selections you make:
If you select the entire contents of a field, or position the insertion point in a field without making any selection, the filter finds (or excludes) all records in which the contents of that field match the exact contents of the selected cell. This technique is especially effective when a field contains a category description or a name that is repeated in records throughout the data source.
If you select a portion of the cell that includes the first character in the cell, the filter finds (or excludes) all records in which the field begins with the selection. If you want to see only those products that begin with the letter A, for example, find any product that begins with that letter, select the first character in its name, and click the Filter by Selection button.
Finally, if you select a portion of a cell's contents that does not include the first character, the filter shows (or hides) all the records in the query that contain the selected string of characters or numbers anywhere in the target field. This technique is useful for finding records based on the contents of a field that contains variable text rather than consistent entries.
If no record that matches the filter is visible, click the Filter by Form button (or choose Records, Filter, Filter By Form). This option clears away the current contents of the query or table and displays a simple grid containing each of the columns in the query or table with a blank cell under each one. When you click in any of these blank cells, you can enter an expression or select from a drop-down list of unique items contained in each field of the query.
As you might guess, the Filter by Form option is an especially powerful way to search using a form you've created. Figure 35.8 shows the result when you open a form and click the Filter by Form button. If you start your query from a form, you can quickly switch between Form and Datasheet views to refine the display of data.
Figure 35.8. The Filter by Form option lets you enter expressions or choose from a drop-down list to refine the display of data from a query or table.
After entering criteria in the Filter by Form window, click the Toggle Filter button to apply the filter and see its results. If you need to refine the filter, click the Filter by Form button again, and add or remove criteria.
Tip from 
The Filter by Form interface looks simple, but it can be surprisingly powerful. If you enter criteria in multiple fields, Access combines them by using the logical operator And—all the conditions must be true to display a result. Click the Or tab at the bottom of the window to create an additional set of conditions using the Or operator—Access will return records that match any of the sets of conditions you enter.
Regardless of how you create a filter, you can always restore the display of all records by clicking the Toggle Filter button again. Note that the ScreenTip for this button reads Apply Filter or Remove Filter, depending on its current state.
To create complex filters, or to edit an existing filter, choose Records, Filter, Advanced Filter/Sort. The resulting filter window contains a field list and a design grid, identical to the Design view for a query. To refine a filter, add one or more fields to the grid and write criteria expressions to select a subset of records; then click the Apply Filter button to see the result of your filter.