MS Access —

Filtering by Selection

Sometimes you may want to see only certain records in your table. By a table, you display only the records that meet your criteria and hide the records that do not.

Filtering a table by selection.

The table is filtered so that only records that have "WA" in the Region field are displayed.

For example, you could filter a client list to display only clients who live in California.

There are several filter methods:

  • Filter by Selection: The fastest and easiest of the three filter commands. Simply find and select the value you want to use as the filter criteria, and then use Filter By Selection to find all records with the selected value.

  • Filter by Form: Here you type your filter criteria into a blank form that contains all the field names in the table. Works well if you have more than one criteria.

  • Advanced Filter/Sort: The most powerful and complicated filter method. Creating an advanced filter is really not any different from creating a query.

In this lesson, you will learn how to use the fastest and easiest way to filter a list with the nifty Filter by Selection feature.

  1. If it isn't already open, find and open the Employees table.

    The first step is finding a record and field that matches your criterion. For example, to find all the addresses from Minnesota you would put the cursor in any State field that contained MN.

  2. Find the Region field and then right-click any WA value.

    A shortcut menu appears, as shown in figure. Notice that the shortcut menu actually contains four filter-related commands. You can read more about these commands in figure.

  3. Select Filter By Selection from the shortcut menu.

    Another way to filter by selection is to select the record and field that matches your criterion and click the Filter by Selection button on the toolbar.

    Access filters the table so that only records that contain "WA" in the Region field are displayed, as shown in figure. Notice that the bottom of the table window tells you the number of records that match your filter criteria. Also the message (Filtered) indicates that the table is currently being filtered.

    Here's how to remove a filter:

  4. Click the Remove Filter button on the toolbar.

    All the records in the table are displayed.

    The opposite of Filter by Selection is , which filters all records that don't contain the criteria value. For example, to find all the addresses that aren't from Minnesota, you would put the cursor in any State field that contained MN.

  5. Find the City field and then right-click any London value. Select Filter Excluding Selection from the shortcut menu.

    This time Access displays all the records that do not contain London in the City field.

  6. Click the Remove Filter button on the toolbar.

    All the records in the table are displayed.

Filter Shortcut Menu Commands
Command Description

Filter by Selection

Finds and displays all records with the selected value.

Filter Excluding Selection

Finds and displays all records that don't contain the selected value.

Filter For

Finds and displays all records that match the text you enter.

Remove Filter/Sort

Removes the applied filter from the table.

To filter by selection:

  1. find the field value on which you want to base the filter.

  2. right-click the field value and select filter by selection from the shortcut menu.

or...

click the field value, then click the filter by selection button on the toolbar.

to filter excluding the selection:

  • right-click the field value you want to exclude and select filter excluding selection from the shortcut menu.

to remove a filter:

  • right-click the filtered table and select remove filter/sort from the shortcut menu.

or...

  • click the remove filter button on the toolbar.