MS Access

Filtering by Form

The Filter by Form window

Filtering by Form makes it easy to create a filter that uses more than one criterion. The Filter by Form window enables you to enter your filter criterion by picking values that you want the filtered records to have.

If you look at figure, you'll notice that several tabs appear at the bottom of the Filter by Form window. If you specify more than one criterion on the same Filter by Form tab, Access treats it as an AND criteria statement, meaning a record must match all the criteria in order to be displayed. For example, you could filter for employees who are from Washington AND who had been with the company for more than five years.

If you specify filter criterion on different tabs, Access treats it as an OR criteria statement, meaning a record has to match the criterion on one tab or the other to be displayed. For example, you could filter for employees from California OR Minnesota.

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

    First you need to display the Filter by Form window.

  2. Click the Filter by Form button on the toolbar.

    The Filter by Form window, which looks like an empty replica of your table, appears as shown in figure.

    The Filter by Form window may already contain a value from a previous filter. If that's not a field that you want to use in your filter, you can press Delete to clear the old criteria.

  3. Press Delete to delete any old filter criteria.

    Next you have to select the field and value you want to use as your criteria.

  4. Click the City field.

    A down arrow appears in the field that the cursor is in. Click this down arrow to see a list of values used in that field.

  5. Click the City list arrow and select London from the list.

    This will display only records whose City field contains "London."

    You can create an AND criteria statement by specifying more than one criterion on the same Filter by Form tab. For example you could filter for employees who are from London AND who have been employed since before 1994.

  6. Click the Title field, click the Title list arrow and select Sales Manager from the list.

    This will display only records for employees who are from London and whose title is Sales Manager.

  7. Click the Apply Filter button on the toolbar.

    Access applies the filter and displays only those records whose City field equals "London" AND whose Title field equals "Sales Manager." Only one record meets the filter criteria. Let's try modifying the query and adding an OR criteria statement.

  8. Click the Filter by Form button on the toolbar.

    If you have another set of criteria or rules to filter records by, click the Or tab at the bottom of the Filter by Form window.

  9. Click the Or tab at the bottom of the Filter by Form window.

    Access displays another blank Filter by Form window. Access will search for any criterion you enter on this tab in addition to your original criterion. You decide to filter for records whose City field equals "London" AND whose Title field equals "Sales Manager" OR whose Title field equals "Vice President, Sales."

  10. Click the Title field and select Vice President, Sales from the list.

    Notice that a new Or tab appears at the bottom of the window. You can use as many "Or" statements as you need to define all your filter criteria.

    Let's see what records our modified filter will find.

  11. Click the on the toolbar.

    Access applies the filter and displays records whose City field equals "London" AND whose Title field equals "Sales Manager" OR whose Title field equals "Vice President, Sales." Two records meet the filter criteria.

  12. Click the on the toolbar.

    Access once more displays all the records in the table.

The following table describes common and examples you can use in your filters.

Common Criteria Operators
Operator Example Description

=

="MN"

Finds records equal to MN.

"MN"

Finds records not equal to MN.

<

<10

Finds records less than 10.

< =

<=10

Finds records less than or equal to 10.

>

>10

Finds records greater than 10.

> =

>=10 AND 5

Finds records greater than or equal to 10 and not equal to 5.

BETWEEN

BETWEEN 1/1/99 AND 12/31/99

Finds records between 1/1/99 AND 12/31/99.

LIKE

LIKE "S*"

Finds text beginning with the letter "S." You can use LIKE with wildcards such as *.


TO FILTER BY FORM:

  1. CLICK THE FILTER BY FORM BUTTON ON THE TOOLBAR.

  2. CLICK THE EMPTY TEXT BOX BELOW THE FIELD YOU WANT TO FILTER, CLICK THE DROP-DOWN ARROW AND SELECT THE VALUE YOU WANT TO USE TO FILTER THE RECORDS.

  3. REPEAT STEP 2 FOR EACH ADDITIONAL FIELD YOU WANT TO USE TO SPECIFY ADDITIONAL FILTER CRITERIA.

  4. IF YOU WANT TO USE OR CRITERIA, CLICK THE OR TAB AT THE BOTTOM OF THE SCREEN TO SPECIFY THE ADDITIONAL FILTER CRITERIA.

  5. CLICK THE APPLY FILTER BUTTON ON THE TOOLBAR.