1-866-245-5224 sales@keystonelearning.com

Access 2007: Advanced Filter

Access 2007: Advanced Filter

For all other filter options, you can always use the Advanced Filter feature. With this feature, you can write expressions to filter your data based on one or more fields.

For example, if you want to find records that contain dates that occur within a specific time frame that match text in a different field, you might have to write the filter criterion yourself.

Figure 29. Advanced Filter View

Applying an Advanced Filter

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 Advanced Filter

4.       Move fields from the Table/Form to the Filter Properties area

5.       Write your expressions

7.       Choose the Toggle Filter command in the Sort & Filter group on the Home tab



Filter Expressions


To Filter For…

Sample Criterion

To Display

Records with exact   matches


Records containing “Can”

Records that don’t match   the criterion

Not Can

Records that don’t   contain “Can”

Records that begin with   one or more specific characters


Records where the text   begins with “Can” such as Canada, Cancun, etc…

Records that don’t begin   with one or more specific character

Not Can*

Records where the text   does not begin with “Can”

Records that contain a   specific string

Like *can*

Records that contain   “Can” somewhere in the text such as “Duncan,” “Cantor,” etc…

Records that end with   one or more specific characters

Like *can

Records that end with   “can” such as “Duncan,” “Toucan,” etc…

A specific range

Like “[S-U]*”

Records that begin with   the letters S, T or U.

Multiple values

“Can” or “Con”

Records that contain   “can” or “con”

A list of values

In (“Asia”, “Africa”,  “Antarctica”, “North America”)

Records for any item in   the list including Asia, Africa, Antarctica and North America

Null values

Is Null

Records that contain no   values in a field

Non-Null values

Is Not Null

Records that contain   fields that have been filled in