Filtering Table Data

Access lets you apply a filter to specify the records that appear in the Datasheet view of a table or a query result set. For example, if you want to view only those customers located in Germany, you use a filter to limit the displayed records to only those whose Country field contains the text Germany. Access gives you four different ways to apply filters to the data in a table:

  • graphics/filter_by_selection.gif Filter by Selection is the fastest and simplest way to apply a filter. You establish the filter criteria by selecting all or part of the data in one of the table's fields; Access displays only records that match the selected sample. With Filter by Selection, you can filter records based only on criteria in a single field of the table.

  • Filter Excluding Selection functions opposite to Filter by Selection all records except those matching the selection appear when you apply the filter.

  • graphics/removes_filter.gif Filter by Form is the second fastest way to apply a filter. You enter the filter criteria into a blank datasheet form of the table; Access displays records that match the combined criteria in each field. Use Filter by Form to quickly filter records based on criteria in more than one field.

  • graphics/advanced_filter.gif Advanced Filter/Sort is the most powerful type of filter. With an advanced filter/sort, you can make an Access filter do double duty because you also can add a sort order on one or more fields. The Advanced Filter/Sort icon doesn't appear on the toolbar.

Filtering by Selection

Creating a Filter by Selection is as easy as selecting text in a field. When you apply the filter, Access uses the selected text to determine which records to display. Table 7.1 summarizes which records are displayed, depending on how you select text in the field. In all cases, Access applies the filter criteria only to the field in which you have selected text. Filter by selection lets you establish filter criteria for only a single field at one time.

Table 7.1. How Selected Text Affects Filter by Selection

Selected Text

Filter Effect

Entire field

Displays only records whose fields contain exactly matching values

Beginning of field

Displays records in which the text at the beginning of the field matches the selected text

End of field

Displays records in which the text at the end of the field matches the selected text

Characters anywhere in field

Displays records in which any part of the field matches the selected text

To create a Filter by Selection on the Customers table (displaying only those customers located in Germany), follow these steps:

  1. graphics/opening_table_displays.gif If necessary, open the Customers table in Datasheet view and use the scroll bars to make the Country field visible in the Table window.

  2. Select all the text in the Country field of the first record in the Customers table. (This entry should be Germany.)

  3. graphics/filter_by_selection.gif Click the Filter by Selection toolbar button or choose Records, Filter, Filter by Selection. Access applies the filter as shown in Figure 7.9.

    Figure 7.9. Applying "Germany" as a selection filter results in the filtered Datasheet view shown here.

    graphics/07fig09.jpg

graphics/apply_filter.gif graphics/toolbars_remover_filter.gif Notice that the Apply Filter toolbar button is now displayed in active status (a contrasting background color), indicating that a filter is being applied to the table, and the ToolTip changes to Remove Filter. The legend (Filtered) also is added to the record selection and status bar at the bottom of the Table window. To remove the filter, click the activated Apply Filter button.

Tip

graphics/find.gif

Use the Find and Replace dialog to quickly locate the first record of a group you're interested in filtering and then apply a Filter by Selection.


As mentioned previously, you can also apply a Filter by Selection based on partially selected text in a field. Figure 7.10 shows the Customers table with a different Filter by Selection applied this time, only the letters "er" in the Country field were selected. You must remove the previous filter before applying a new filter for the entire table, rather than the filtered records.

Figure 7.10. Selecting only a part of the field in this case the letters "er" displays records containing the partial selection in any part of the field.

graphics/07fig10.jpg

Tip

You can apply a Filter by Selection to more than one field at a time. For example, after applying a Filter by Selection to display only those customers in Germany, you could then move to the City field and apply a second Filter by Selection for Berlin. The resulting table would include only those customers in Berlin, Germany. An easier way to apply filters based on more than one field value is to use a Filter by Form, described in the "Filter by Form" section coming up.


Using the Filter For Option

The Filter For option is a quick method for applying a filter to a single field. To use the Filter For feature, do this:

  1. Right-click the field on which you want to filter the table, and choose the Filter For text box in the context menu.

  2. In the text box, type the value you want to filter on, such as USA for the Country field of the Customers table, and press Enter to apply the filter.

graphics/toolbars_remover_filter.gif To remove the filter, right-click anywhere in the Datasheet window and choose Remove Filter/Sort. Alternatively, click the toolbar's Remove Filter/Sort button. Use this shortcut to remove any filter you've applied to the table.

Filtering by Form

Filtering by form is slightly more complex than filtering by selection because it lets you filter records based on criteria in more than one field at a time. For example, you saw in the preceding section how to use a Filter by Selection to view only those customers in Germany. To further limit the displayed records to those customers located in Berlin, Germany (and not Berlin, New Hampshire), use a Filter by Form.

In a Filter by Form, Access displays a blank form for the table (see Figure 7.11). This window is called a form to distinguish it from the Table Datasheet window, although it's not the same as the data-entry forms discussed later in this book. You can combine criteria in a Filter by Form with a logical Or operator or a logical And operator. For example, you can filter the Customers table to display only those customers in the United States or Canada. As another example, you could filter the Customers table to display only those customers in the United States and in ZIP codes beginning with the digit 9 (such as 94609 or 90807).

Figure 7.11. The Filter by Form variation of Datasheet view has a single row in which you add filter criteria. Each field has a drop-down list of values you can choose for the filter.

graphics/07fig11.gif

Tip

Verify that all the fields of the filter form are empty before designing a new filter. The last filter expression you apply appears in the filter form when you open it. For instance, the Country field contains Like "*er*" if you tested the partial selection example in the preceding section.


To create a Filter by Form on the Customers table to display only those customers in the United States or Canada, follow these steps:

  1. graphics/opening_table_displays.gif If necessary, open the Customers table in Datasheet view.

  2. graphics/removes_filter.gif Click the Filter by Form toolbar button or choose Records, Filter, Filter by Form to display the Filter by Form window (refer to Figure 7.11).

  3. Make the Country field visible in the Filter by Form window if necessary. (The CustomerID and CompanyName fields in the figures have been frozen, as described previously in this chapter.)

  4. Click inside the Country field and open the Country list box, or press F4. The drop-down list contains all the unique values in the Country field.

  5. Select Canada in the list box, as shown in Figure 7.12. Access automatically adds the quotation marks around the value you select and enters it into the Country field form box.

    Figure 7.12. The drop-down list in the Filter by Form datasheet lets you select a single criterion on which to filter the field.

    graphics/07fig12.gif

  6. Click the Or tab at the bottom of the Filter by Form window. Access combines criteria that you enter on separate tabs in the Filter by Form window with a logical Or operator. When you add an Or operator, a tab for another Or operator appears.

  7. Click the arrow to open the Country list box or press F4. Select USA from the drop-down list (see Figure 7.13).

    Figure 7.13. Clicking the Or tab of the Filter by Form window opens another empty row in which you can select another criterion. Each time you add an Or criterion, an additional disabled Or tab appears at the bottom of the window.

    graphics/07fig13.gif

  8. graphics/apply_filter.gif Click the Apply Filter button. Access applies the new filter to the table, displaying the records shown in Figure 7.14.

    Figure 7.14. The Customers table in Datasheet view displays the result of applying the "Canada" Or "USA" filter.

    graphics/07fig14.jpg

Note

graphics/properties_window.gif

graphics/design_view.gif Access stores the last filter you applied as the value of the table's Filter property. To view the filter value, change to Table Design view and click the Properties button to open the Properties window for the table. For the preceding example, the filter value is ((Customers.Country="Canada")) OR ((Customers.Country="USA")). The parenthesis pairs are superfluous for this filter.


You can also combine filter criteria in a logical And operator by entering criteria in more than one field on the same tab of the Form window. For example, you want to filter the Orders table to find all orders handled by Nancy Davolio and shipped to France. You easily can use a Filter by Form to do so, as the following example shows:

  1. Open the Orders table and freeze the OrderID, Customer, and Employee fields. Then position the ShipCountry field so that it's visible (see Figure 7.15). Freezing the fields isn't an essential step, but it makes setting up the filter and viewing the filtered data easier.

    Figure 7.15. Simplify the filtering process by freezing the first three fields of the Orders table.

    graphics/07fig15.jpg

  2. graphics/removes_filter.gif Click the Filter by Form toolbar button to display the Filter by Form window.

  3. graphics/clear_grid_toolbar.gif Click the Clear Grid toolbar button or choose Edit, Clear Filter to clear any previous filter criteria from the Filter by Form grid.

  4. Use the drop-down list in the EmployeeID field to select Davolio, Nancy, and then use the drop-down list in the ShipCountry field to select France. You must manually add quotes around a text criterion that includes a comma (see Figure 7.16).

    Figure 7.16. The criterion in the EmployeeID field is based on the lookup field that displays the LastName and FirstName values from the Employees tables, separated by a comma and space. Matching a composite criterion requires surrounding the value with quotes.

    graphics/07fig16.jpg

  5. graphics/apply_filter.gif Click the Apply Filter button. Access applies the new filter to the table, displaying the records shown in Figure 7.17. This filter shows only those records for orders that were handled by Nancy Davolio and shipped to France.

    Figure 7.17. The EmployeeID and Country filter criteria shown in Figure 7.16 result in the following Datasheet view of the Orders table.

    graphics/07fig17.jpg

Tip

You also can apply the Filter by Form feature to Access forms that are bound to tables or queries. With forms that display field values in text boxes, clicking the Filter by Form button clears the text boxes and adds the Look For and Or tabs to the bottom of the form. You type search value(s) in the appropriate text box(es) for the And operator, Or operator, or both.


graphics/troubleshooting.gif

If Access doesn't return the records you expected, try the solution in the "Troubleshooting" section at the end of the chapter.




Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net