Applying Filters to Access Queries

When you build a query to view information in your database, you set criteria to narrow your results down to just the records you care about. The figure below shows a simple example, where only the companies located in the state of Texas are displayed. The query itself does the work of selecting and returning matching rows, and the criteria you type into the query's design grid determine which rows make the cut.

Writing criteria directly into a query works well when those criteria rarely change. The trouble starts the moment you want to look at the data a slightly different way. Suppose you built the query above to return Texas companies, but now you want to see companies in California instead, or you want to switch from filtering on the State field to filtering on the City field. Editing the query each time means returning to Design view, changing the criteria expression, and re-running the query. That round trip is fine occasionally, but it becomes tedious when you are exploring a result set and want to slice it several different ways in quick succession. It also permanently alters the saved query, which is not what you want if the original criteria are still useful.
This is where the two filter features, Filter by Selection and Filter by Form, earn their place. A filter is applied on top of a result set that is already on screen. It temporarily hides the rows you do not want without touching the underlying query design, and you can clear it instantly to get every record back. Think of query criteria as the permanent shape of the result set and filters as a disposable overlay you can apply, change, and remove as often as you like while you work.
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 holds the value you want to base the selection on. For example, if you want to filter the complete result set returned by a query that has no criteria, such as every record in the Clients table, and you want to view only the clients located in Texas, you would do the following:

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.
   

   

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.
   

   

You will now see only those clients that are located in Texas. Notice what made this work: you never opened Design view and you never typed a criteria expression. Access read the value sitting under your cursor — TX — and matched every record that shared it. This is the defining trait of Filter by Selection: the data already on screen supplies the filter value, so the feature is fastest when you can already see an example of what you want. If you wanted clients in California instead, you would simply click into a record showing CA and apply the selection again. Selecting only part of a field's contents before you click lets Access offer related matches, such as "begins with" or "contains," which is useful for text fields where you remember a fragment but not the whole value.

Filter by Selection is quick, but it has a built-in limit: you can only filter on values that already appear in the result set, and applying conditions across several fields means selecting, filtering, selecting again, and filtering again. Filter by Form solves both problems by taking you to a separate screen where you specify all of your criteria up front. To filter by form, perform steps 1 and 2 from the Filter by Selection steps to get your result set on screen. Then click the 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, or pick it from the drop-down list that each column offers. 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.

You will then see the clients located in Texas. Because the form gives you a row of empty fields, you can enter conditions in several columns at once. Values typed across the same row are combined with AND logic, so entering TX in State and Dallas in City returns only the clients that satisfy both. To express OR logic — clients in Texas or California — you use the Or tab described later, which adds another blank row for an alternative set of conditions. To remove the current filter for either method of filtering, click the Remove Filter toolbar button.

The Remove Filter button is actually a toggle. Clicking it once strips the filter and shows every record again; clicking it a second time re-applies the same filter, so you can flip back and forth between the filtered and unfiltered views without re-entering anything. This makes it easy to compare a subset against the full result set at a glance. The following section discusses how to practice performing a 1) Filter by Selection and a 2) Filter by Form.

For further practice, try this on your own in Microsoft Access, using another instance.

1. To start with, use the query called qryClientsAndProjects in the project database. Click the View button to work with the results of this query.
2. You now have the results set, with the client named "Network Consultants, Inc." highlighted. Click the Filter by Selection toolbar button.
3. Now you can see the four records filtered from the rest of the query's result set. Remove the filter by clicking the Remove Filter toolbar button.
4. Here are all the records once again. Now it's time to work with the Filter by Form feature. Click the Filter by Form toolbar button.
5. You can see the filter you just set with the Filter by Selection feature. At this point, choose a different company for which to filter. Click the drop-down for the Company field.
6. Now choose the company called "ABC Webworks".
7. The company chosen will be displayed in the Company drop-down. Now it's time to apply the filter. Click the Apply Filter button.
8. There you go: the new results with the applied filter.

Filtering by form: Filter by Form lets you enter criteria into a single row on the datasheet. Clicking the Filter by Form button transforms the datasheet into a single row containing a drop-down list in every column. The drop-down list contains all the unique values for that column, which is convenient because it spares you from typing and guarantees you match a value that actually exists in the data. An Or tab at the bottom of the window lets you specify OR conditions for each group. Choose

Advanced => Filter by Form 

in the Sort & Filter group of the Ribbon to enter Filter by Form mode, shown in Figure 3.3.
Using Filter by Form lets you set multiple conditions for filtering at one time. Notice the Or tab at the bottom of the window.

Select values from the combo boxes or type values you want to search for in the field. If you want to see records where the Category is Trucks or SUVs, select Trucks from the Category drop-down list, select the Or tab at the bottom of the window, and then select SUVs from the Category drop-down list. Each Or tab represents a separate set of conditions, and a record is shown if it matches any one of those sets. To see records where Category is SUV and QtyInStock is 1, select SUV from the Category drop-down and type 1 in QtyInStock; placing both conditions on the same row combines them with AND so that only records meeting both are returned. Once you enter the desired criteria, click the Toggle Filter command to apply the filter. Enter as many conditions as you need using the Or tab. If you need even more advanced manipulation of your selections, you can choose Advanced > Advanced Filter/Sort from the Sort & Filter group of the Ribbon to get an actual Query by Example (QBE) screen that you can use to enter more complex criteria. The QBE screen is the same grid you use when designing a query, which shows how closely filtering and querying are related: a filter is essentially a lightweight, throwaway query applied to a result set that already exists.

1. To start with, you will use this query called qryClientsAndProjects in the project database. Click the View button to work with the results of this query.
2. You now have the results set, with the client named “Network Consultants, Inc.” highlighted. For the first part of this simulation you will practice using Filter by Selection. Click the Filter by Selection toolbar button.
3. Now you can see the four records filtered from the rest of the query’s result set. Remove the filter by clicking the Remove Filter toolbar button.
4. Here are all the records once again. Now it’s time to work with the Filter by Form feature. Click on the Filter by Form toolbar button.
5. You can see the filter you just set with the Filter by Selection feature. At this point, you are going to choose a different company for which to filter. Click the arrow in the drop-down list for the Company field.
6. Now click on the company called “ABC Webworks”.
7. The company chosen will be displayed in the Company drop-down. Now it’s time to apply the filter. Click the Apply Filter button.
8. There you go; the new results with the applied filter.

• Apply a filter to view select records in an Access database:
  Filtering is a useful way to see only the data that you want displayed. You can use filters to display specific records in a form, report, query, or datasheet, or to print only certain records from a report, table, or query. By applying a filter, you are able to limit the data in a view without altering the design of the underlying object. This article explains the types of filters in Access and how to apply, save, or remove filters in an Access 2010 client database. It does not apply to filtering records in an Access 2010 web database.
• How filters are useful:
  Since the view you get after you apply a filter contains only the records with the values you selected, the rest of the data remains hidden until you clear the filter. A filter you apply to a datasheet can also be saved with the object, so the next time you open it the same view is ready for you; saving the parent query or form preserves the most recently applied filter as its default filter property.
  Note: Columns in datasheets and controls in forms and reports that are bound to expressions do not support filtering. There are several types of filters, and some of them are easy to apply and remove. Access 2010 includes a set of common filters built into every view, and the commands available to you depend on the data type and the values of the field you click. For a text field you might see options such as Begins With or Contains; for a number field, Equals or Between; and for a date field, a rich set of period choices. For example, to view the records of people whose birthdays fall during a specific month, click the BirthDate column, then on the Home tab in the Sort & Filter group click Date Filters, and select the required period. Because these common filters are aware of the field's data type, they spare you from remembering the exact syntax an equivalent query criterion would require.
  

  

To summarize when to reach for each tool: use Filter by Selection when an example of the value you want is already visible in the result set and you want a single-field filter in one click; use Filter by Form when you need to combine conditions across several fields, express OR logic with the Or tab, or pick values from drop-down lists rather than hunting for an example record; and fall back to query criteria when the condition is permanent and should travel with the saved query. Filters give you fast, reversible exploration, while criteria give you a durable, repeatable definition of a result set.
In the next lesson, you will learn how to use a parameter with your query to specify criteria dynamically. Where a filter is something you apply by hand after the results appear, a parameter prompts the user for a value each time the query runs, giving you the convenience of changeable criteria with the permanence of a saved query — a natural next step once you are comfortable filtering by hand.