Refining Queries  «Prev  Next»
Lesson 2 Applying filters to queries
Objective Use Filter by Selection and Filter by Form while in a query's datasheet.

Applying Filters to Access Queries

When creating queries to view information in your database, you can set criteria to narrow your choices. You can see a simple example of this in the figure below, where companies in the state of Texas are displayed.
Example figure
Example figure

Using criteria directly in a query is limiting when you decide you need to change the criteria value or the field being used with the criteria. When you need to do this, you can take advantage of tw filter features, Filter by Selection and Filter by Form.

Filter by Selection lets you temporarily reduce the results of a query using the value in any field of those results. To use Filter by Selection, place the cursor in the field on the datasheet that has the value on which to base the selection. For example, if you want to filter the complete results set displayed from a query without criteria, such as all the records in the Clients table, and you want to view all the clients located in Texas, you would do the following:

Filtering Access by form Selection
  1. Create a simple Select query based on the Clients table, including all fields.
  2. Open the query in Datasheet view by clicking the View toolbar button
    View toolbar button
    View toolbar button
  3. Place the cursor in the State field of any record that has TX for the state.
  4. Click the Filter by Selection toolbar button
    Filter by Selection toolbar button
    Filter by Selection toolbar button

You will now see only those clients that are located in Texas.

Filtering using Filter by Form

Filter by Form takes you to a different screen to specify the criteria you want to use in your filter. To filter by form, perform steps 1 and 2 from the Filter by Selection steps. Then click the Filter by Form toolbar button
Filter by Form toolbar button
Filter by Form toolbar button
Now you will be presented with what looks like a Datasheet view with no records in it. Click in the field you want to filter on and type the value you want to match. To continue with the example from Filter by Selection, you would click in the State field of the form and type TX , then click the Apply Filter toolbar button.
Apply Filter toolbar button
Apply Filter toolbar button

You will then see the clients located in Texas.
To remove the current filter for either methods of filtering, click on the Remove Filter toolbar button
Remove Filter toolbar button
Remove Filter toolbar button
In the next lesson, you will learn how to use a parameter with your query to specify criteria dynamically.

Filtering By Form Selection

Click the Exercise link below to practice performing a Filter by Selection and a Filter by Form.
Filtering By Form Selection