Filtering Information in a Table


Sorting the information in a table organizes it in a logical manner, but you still have the entire table to deal with. To locate only the records containing (or not containing) specific information, filtering is more effective than sorting. For example, you could quickly create a filter to locate only customers who live in Seattle, only items that were purchased on January 13, or only orders that were not shipped by standard mail.

You can apply simple filters while viewing information in a table or form. To filter information by multiple criteria, you can apply additional filters to the results of the first one.

Wildcards

If you want to locate records containing certain information but aren't sure of all the characters, or want your search to return multiple variations of a base character set, you can include wildcard characters in your search criteria. The most common wildcards are:

  • * (asterisk) represents any number of characters For example

    LastName = Co* returns entries including Colman and Conroy

  • ? (question mark) represents any single alphabetic character. For example

    FirstName = er?? returns entries including Eric and Erma

  • # (number sign) represents any single numeric character. For example

    ID = 1## returns any ID from 100 through 199

Tip

Access supports several sets of wildcards. For more information on these, search Access Help for wildcards and read the topic titled "Access wildcard character reference."


When searching for information in a text field, you can also use the Contains text filter to locate records containing words or character strings.


In this exercise, you will filter records by a single criterion and then by multiple criteria.

Tip

The Filter commands you will use in this exercise are available in the Sort & Filter group on the Home tab, on the column menu displayed when you click a column header arrow, and on the shortcut menu displayed when you right-click a column. However, not all Filter commands are available in each of these places.


USE the 02_FilterTable database. This practice file is located in the Chapter06 subfolder under SBS_Access2007.

OPEN the 02_FilterTable database, and then open the Customers table in Datasheet view.


1.

In the City field, click any instance of Vancouver.

2.

On the Home tab, in the Sort & Filter group, click the Selection button, and then in the list, click Equals "Vancouver".

The number of customers displayed in the table (and on the status bar at the bottom of the table) changes from 110 to 6, because only six customers live in Vancouver.

Access displays a small filter icon at the right side of the City column header to indicate that the table is filtered by that field. The Toggle Filter button in the Sort & Filter group and the Filter status on the status bar changes to Filtered.

Important

When you filter a table, Access doesn't remove the records that don't match the filter; it simply hides them.

3.

In the Sort & Filter group, click the Toggle Filter button.

Access removes the filter and displays all the records. If you click the Toggle Filter button again, the last filter used will be reapplied.

Suppose that you want a list of all customers with postal codes starting with 880.

4.

Click the PostalCode column header arrow, point to Text Filters, and then click Begins With.

Tip

The sort and filter options displayed when you click the column header arrow are determined by the field type. If this were a numeric field, then the submenu would be Number Filters and different options would be listed. U.S. Zip Codes and international postal codes are usually text fields to allow for the ZIP+4 codes.

The Custom Filter dialog box opens.

5.

In the PostalCode begins with box, type 880. Then click OK.

The filtered table includes 30 records that have postal codes starting with 880.

6.

In the Sort & Filter group, click the Toggle Filter button to remove the filter and display all the records.

Suppose you want to display only those customers who live outside of the United States.

7.

In the Country column, right-click any instance of USA, and then click Does Not Equal "USA".

Access displays all the customers from countries other than the United States (in this case, only Canada).

8.

Remove the filter, save and close the Customers table, and then open the Orders table in Datasheet view.

9.

In the EmployeeID field, right-click Emanuel, Michael, and then click Equals "Emanuel, Michael".

Troubleshooting

If you continued with the 01_SortTable database from the previous exercise, the EmployeeID field does not list employee names. To complete this exercise, you must use the 02_FilterTable database.

10.

In the OrderDate field, right-click 2/1/2007, and then click On or After 2/1/2007.

Tip

To see a list of the available options for date filters, right-click any cell in the OrderDate column and then point to Date Filters.

You now have a list of orders placed with the selected employee on or after the specified date. You could continue to refine the list by filtering on another field, or you could sort the results by a field.

Tip

After you locate the information you want, you can display the results in a form or report. To do so, on the Create tab, click the object you want to create.

CLOSE the Orders table without saving changes, and then close the 02_FilterTable database.


Tip

You can filter records while displaying them in a form by using the same commands as you do to filter forms in a table.


Expressions

In Access lingo, expressions are synonymous with formulas. An expression is a combination of operators, constants, functions, and control properties that evaluates to a single value. Access builds formulas using the format a=b+c, where a is the result and =b+c is the expression. You can use an expression to assign properties to tables or forms, to determine values in fields or reports, as part of queries, and so on.

The expressions you use in Access combine multiple criteria to define a set of conditions that a record must meet to be included in the result of a filter or query. Multiple criteria are combined using logical, comparison, and arithmetic operators. Different types of expressions use different operators.

The most common logical operators are:

  • And. This operator selects records that meet all the specified criteria.

  • Or. This operator selects records that meet at least one of the criteria.

  • Not. This operator selects records that don't match the criteria.

Common comparison operators include:

  • < (less than)

  • > (greater than)

  • = (equal to)

You can combine these basic operators to form:

  • <= (less than or equal to)

  • >= (greater than or equal to)

  • <> (not equal to)

The Like operator is sometimes grouped with the comparison operators and is used to test whether or not text matches a pattern.

You use arithmetic operators with numerals. The most common are:

  • + (add)

  • - (subtract)

  • * (multiply)

  • / (divide)

A related operator, & (a text form of +) is used to concatenate (combine) two text strings.




MicrosoftR Office AccessT 2007 Step by Step
MicrosoftR Office AccessT 2007 Step by Step
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 127

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