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.

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:

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.

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

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.

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

The following section dicusses 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 the column. 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 fi ltering 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. To see records where Category is SUV and QtyInStock is 1, select SUV from the Category drop-down and type 1 in QtyInStock. 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.

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. This article 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 records with the values that you selected, the rest of the data remains hidden until you clear the filter.
  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 contains some common filters that are built into every view. The availability of filter commands depends on the type and values of the field. For example, to view the records of people whose birthdays fall during a specific month, click the BirthDate column, on the Home tab in the Sort and Filter group, click Date Filters, and then select the required date period.
  

  

In the next lesson, you will learn how to use a parameter with your query to specify criteria dynamically.