When using common filters and Filter by Selection, you’re only able to filter one field at a time. However, when you want to filter based on several fields, you can use the Filter by Form feature.
When using Filter by Form, Access creates a blank form or datasheet that is similar to the original form or datasheet. On this blank object, you can enter data into as many fields as you want to in order to specify your criteria.
All criteria entered on the initial form must be matched before a record result will be returned. This is an AND criteria. But, you can also create an OR criteria by using the OR tabs located on the lower-left corner of the Filter by Form window. Each time you enter criteria on one OR tab, a new OR tab will appear so you can continue to add criteria.
Figure 28. Filter by Form Criteria Tabs
Applying a Filter by Form
1. Open the Table or Form in Datasheet View you want to filter
2. Choose the Advanced command in the Sort & Filter group on the Home tab
3. Choose Filter by Form
4. Enter your Filter criteria
5. Choose the Toggle Filter command in the Sort & Filter group on the Home tab
6. Click the Toggle Filter command again to display all records or click Filter by Form again to modify the filter criteria
Removing a Common Filter
1. Click the field heading of the column you want to remove the filter from
2. Choose Clear Filter From [Field Name]