The Filter By Form command provides a quick and easy way to filter a table based on the information in several fields. If you open a table and then click the Filter By Form button, what you see looks like a simple datasheet. However, each of the blank cells is a combo box with a scrollable drop-down list of all the entries in that field.
You can make a selection from the list and click the Apply Filter button to display only the records containing your selection.
Using Filter By Form on a table that has only a few fields, such as this one, is easy. But using it on a table that has a few dozen fields gets a bit cumbersome. Then it’s easier to use Filter By Form in the form version of the table. If you open a form and then click Filter By Form, you see an empty form. Clicking in any box and then clicking its down arrow displays a list of all the entries in the field.
If you make a selection and click the ApplyFilter button, clicking the Next Record navigation button displays the first record that meets your selection criteria, then the next, and so on.
Tip
Filter By Form offers the same features and techniques whether you are using it in a form or a table. Because defining the filter is sometimes easier in a form and viewing the results is sometimes easier in a table, you might consider using AutoForm to quickly create a form for a table. You can then use the form with Filter By Form rather than the table, and then switch to Datasheet view to look at the results.
In this exercise, you will try to track down a customer whose last name you have forgotten. You’re pretty sure the name starts with S and the customer is from California or Washington, so you’re going to use Filter By Form to try to locate the customer’s record.
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My DocumentsMicrosoft PressAccess 2003 SBSQueriesFilterForm folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
Click Forms on the Objects bar, and double-click Customers to open the Customers form in Form view.
Click the Filter By Form button on the toolbar.
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.
Click the second Name box (last name), type
s*, and press
to tell Access to display all last names starting with S.Access converts your entry to the proper format, or syntax, for this type of expression: Like "s*".
Click the Region box, and click CA in the drop-down list.
Click the Apply 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 navigation bar at the bottom of the form indicates that three filtered records are available.
Click the Filter By Form button to switch back to the filter.
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.
To add the customers from another state, click the Or tab.
This tab has the same blank cells as the "Look for" tab. You can switch back and forth between the two tabs to confirm that your criteria haven’t been cleared.
Type
s*in the LastName box, type or clickWAin the Region box, and then click the Apply Filter button.You can scroll through the filtered Customers form to view the six matched records.
Close the Customers form.
CLOSE the GardenCo database.