Adding Criteria

The criteria that you set for your query determine how the field information found in the selected fields appears in the completed query. You set criteria in the query to filter the field data. The criteria that you set in a query are similar to the criteria that you worked with when you used the filtering features in Lesson 14, "Sorting, Filtering, and Indexing Data."

For example, suppose you have a query where you have selected fields from an Employee table and a Department table (which are related tables in your company database). The query lists the employees and their departments. You would also like to list only employees that were hired before March 2003. This means that you would set a criteria for your Start Date field of <03/01/2003. Using the less-than sign (<) simply tells Access that you want the query to filter out employee records where the start date is before (less than) March 1, 2003.

To set criteria for a field in your query, follow these steps:

1. In Query Design view, click the Criteria row in the desired field's column.

2. Type the criteria you want to use (see Figure 16.4).

   Figure 16.4. Enter your criteria into the Criteria row of the appropriate field's column.

   

3. Queries can contain multiple criteria. Repeat steps 1 and 2 as needed to add additional criteria to field columns in the query.

Query criteria can act both on alphanumeric field data (text) and numeric data (dates are seen by Access as numerical information). For example, suppose you have a Customer table that lists customers in two states: Ohio (OH) and Pennsylvania (PA). The criterion used to filter the customer data in a query so that only customers in PA is shown in the query results would be PA. It's that simple.

When you work with criteria, symbols are used (such as the less-than sign that appears in the criteria in Figure 16.4) to specify how the query should evaluate the data string that you place in the Criteria box. Table 16.1 provides a list of some of these symbols and what you use them for.

Table 16.1. Sample Criteria for Queries

Symbol	Used For
< (less than)	Matching values must be less than (or before in the case of dates) the specified numerical string.
> (greater than)	Matching values must be greater than (or after in the case of dates) the specified numerical string.
<= (less than or equal to)	Matching values must be equal to or less than the value used in the criteria.
>= (greater than or equal to)	Matching values must be equal to or greater than the value used in the criteria.
= (equal to)	Matching values must be equal to the criteria string. This symbol can be used both with text and numeric entries.
Not	Values matching the criteria string will not be included in the results. For example, Not PA filters out all the records in which PA is in the state field.