Filtering Information by Using a Form

The Filter By Form command, available from the Advanced Filter Options list, provides a quick and easy way to filter a table based on the information in several fields. When you invoke this command within a table, Access displays a Look For tab containing a filtering form that looks like an empty datasheet. However, each of the blank cells is actually a combo box containing a list of all the entries in that field. You can select a filter criterion from the list, or enter a new one. Then you click the Toggle Filter button to display only the records containing your selected criteria.

Using Filter By Form on a table that has only a few fields, such as the one shown above, is easy. But using it on a table that has a few dozen fields gets a bit cumbersome, and it is simpler to find information by using the Filter By Form command in the form version of the table. When you invoke this command within a form, Access filters the form in the same way it filters a table.

In a filtered form, you move between records by clicking the navigation buttons at the bottom of the form window.

In this exercise, you will locate a record by using the Filter By Form command.

1.	In the Navigation Pane, under Forms, double-click Customers. The Customers form opens in Form view. [View full size image]
2.	On the Home tab, in the Sort & Filter group, click the Advanced button, and then in the list, click Filter By Form. The Customers form, which displays the information from one record, is replaced by its Filter By Form version, which has a blank box for each field and the Look For and Or tabs at the bottom.
3.	Click the second text box to the right of the Name label (the box intended to contain the surname), type s*, and then press . Access converts your entry to the proper format, or syntax, for this type of expression: Like "s*".
4.	In the Sort & Filter group, click the Toggle Filter button. Access displays all records including last names starting with S.
5.	Click the Filter By Form button again to switch back to the filter form. Your filter criteria are still displayed. When you enter filter criteria using any method, they are saved as a form property and are available until they are replaced by other criteria.
6.	Click the box to the right of Region, click the arrow that appears, and then in the list, click CA.
7.	Click the Toggle Filter button to see only the customers living in California whose last names begin with S. Access replaces the filter window with the regular Customers form, and the status bar at the bottom of the form indicates that three filtered records are available. [View full size image]
8.	Click the Filter By Form button again to switch back to the filter form.
9.	At the bottom of the form window, click the Or tab. Tip Criteria you enter on the Look For tab are joined with the And operator to reduce the number of possible hits in the underlying table. Criteria entered on the Or tabs tend to increase the number of hits. This tab has the same blank cells as the Look For tab. You can switch between the two tabs to confirm that your criteria haven't been cleared. Tip When you display the Or tab, a second Or tab appears so that you can include a third criterion if you want.
10.	Type s* in the LastName box, type or click WA in the Region box, and then click the Toggle Filter button. You can scroll through the filtered Customers form to view the six records containing WA in the Region field.