To use Filter by Selection
The simplest way of filtering the display of records in a table is use Filter by Selection. This displays only records that have the same value in that field.
- Display the table in datasheet view
- Select the value in a particular field that you have chosen as the filter value
Tip: If you select part of a field, then the filter will be based on that partial selection. For example, if you pick 'F' in the third position of a field and filter this by example, then the results will be exactly that - fields that have an 'F' in the third position of that field.
For instance, you might want to display items that for which you have no stock level, or examine the customers where your contact is the owner.
- On the Standard toolbar, click Filter by Selection
- From the Records menu, select Filter, then select Filter by Selection
The datasheet is refreshed to display only records that pass the selected filter criteria- you should always get at least one record - the one that you selected for the filter.
You may further limit the records that are displayed by adding to the Filter by Selection.
- If you select another field from this filter-limited list, and select Filter by Selection once more, the records displayed will be those the match both criteria
It follows that you can add successive filters by example, until the requirements match the criteria that you require.
To re-display all records
Once a filter is in place, you may want to display all records from the table. If the filter that you have created is complex, Access allows you to 'toggle' the display of records between all records and the filter that you have created.
Once you have set a filter, the Apply Filter button provides you with the ability to toggle the display between the filter that you have created, and displaying all records.
- On the Standard toolbar, click Apply Filter to toggle between filtered records and all records
Tip: This button can also be used to turn off the filter when you have finished using it.
Using Advanced Filters
While Filter by Selection allows you to filter on exact values, it is often necessary to filter the information in your datasheet by a range of values. Access provides an option to Filter by Form. Filter by Form uses a blank record that you can type an example of the data by which you wish to filter into.
You may also use calculated expressions as a basis of the filter that you wish to display. As well as matching the filter value to the field, you can use different operators to create more sophisticated filters. Some of the more common operators are explained in the table below.
For more examples of expressions that you can create, consult the Access help screens.
It should be noted that if you fill in more than one column with a filter expression, then the expressions must all be true for the record to be displayed (sometimes known as 'AND-ing').
Access also allows you to 'OR' several fields together, using the OR tab at the bottom of the filter by example window.
When you select the OR tab, another blank record is displayed at the top of the window, and you may fill in additional criteria for your filter.