Examples of query criteria

Applies to: Microsoft Office Access 2007

When you want to limit the results of a query based on the values in a field, you use query criteria. A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value. For example, = "Chicago" is an expression that Access can compare to values in a text field in a query. If the value for that field in a given record is "Chicago", Access includes the record in the query results.

This topic lists several examples of query criteria. It assumes that you are familiar with designing simple select queries.

To learn more about creating select queries, see the article Select data by using a query.

In this topic

• Introduction to query criteria

• Criteria for Text, Memo, and Hyperlink fields

• Criteria for Number, Currency, and AutoNumber fields

• Criteria for Date/Time fields

• Criteria for other fields

Introduction to query criteria

A criterion is similar to a formula — it is a string that may consist of field references, operators, and constants. Query criteria are also referred to as expressions in Microsoft Office Access 2007.

The following tables shows some sample criteria and explains how they work.

As you can see, criteria can look very different from each other, depending on the data type of the field to which they apply and your specific requirements. Some criteria are simple, and use basic operators and constants. Others are complex, and use functions, special operators, and include field references.

This topic lists several commonly used criteria by data type. If the examples given in this topic do not address your specific needs, you might need to write your own criteria. To do that, you must first familiarize yourself with the full list of functions, operators, special characters, and the syntax for expressions referring to fields and literals. For more information, see the articles listed in the See also section.

Here, you will see where and how you add the criteria. To add a criteria to a query, you must open the query in Design view. You then identify the fields for which you want to specify criteria. If the field is not already in the design grid, you add it by either dragging it from the query design window to the field grid, or by double-clicking the field (Double-clicking the field automatically adds it to the next empty column in the field grid.). Finally, you type the criteria in the Criteria row

Criteria that you specify for different fields in the Criteria row are combined by using the AND operator. In other words, the criteria specified in the City and BirthDate fields are interpreted like this:

City = "Chicago" AND BirthDate < DateAdd("yyyy", -40, Date())

The City and BirthDate fields include criteria. Only records where the value of the City field is Chicago will satisfy this criterion. Only records of those who are at least 40 years old will satisfy this criterion. Only records that meet both criteria will be included in the result.

What if you want only one of these conditions to be met? In other words, if you have alternate criteria, how do you enter them?

If you have alternate criteria, or two sets of independent criteria where it is sufficient to satisfy one set, you use both the Criteria and the or rows in the design grid.

Criteria for Number, Currency, and AutoNumber fields

The following examples are for the UnitPrice field in a query that is based on a table that stores products information. The criterion is specified in the Criteria row of the field in the query design grid.

Criteria for Date/Time fields

The following examples are for the OrderDate field in a query based on a table that stores Orders information. The criterion is specified in the Criteria row of the field in the query design grid.

Criteria for other fields

Yes/No fields In the Criteria row, type Yes to include records where the check box is selected. Type No to include records where the check box is not selected.

Attachments In the Criteria row, type Is Null to include records that do not contain any attachments. Type Is Not Null to include records that contain attachments.

Lookup fields There are two types of Lookup fields: those that look up values in an existing data source (by using a foreign key), and those that are based on a list of values specified when the Lookup field is created.

Lookup fields that are based on a list of specified values are of the Text data type, and valid criteria are the same as for other text fields.

The criteria you can use in a Lookup field based on values from an existing datasource depend on the data type of the foreign key, rather than the data type of the data being looked up. For example, you may have a Lookup field that displays Employee Name, but uses a foreign key that is of the Number data type. Because the field stores a number instead of text, you use criteria that work for numbers; that is, >2.

If you do not know the data type of the foreign key, you can inspect the source table in Design view to determine the data types of the field. To do this:

• 1. Locate the source table in the Navigation Pane.

  2. Open the table in Design view by either:

     • Clicking the table, and then pressing CTRL+ENTER

     • Right-clicking the table, and then clicking Design View.

  3. The data type for each field is listed in the Data Type column of the table design grid.

Multivalued fields Data in a multivalued field are stored as rows in a hidden table that Office Access 2007 creates and populates to represent the field. In query Design view, this is represented in the Field List by using an expandable field. To use criteria for a multivalued field, you supply criteria for a single row of the hidden table. To do this:

• 1. Create a query containing the multivalued field, and open it in Design view.

  2. Expand the multivalued field by clicking the plus symbol (+) next to it — if the field is already expanded, this is a minus symbol (-). Just below the name of the field, you will see a field representing a single value of the multivalued field. This field will have the same name as the multivalued field, with the string .Value appended.

  3. Drag the multivalued field and its single value field to separate columns in the design grid. If you want to see only the complete multivalue field in your results, clear the Show check box for the single value field.

  4. Type your criteria in the Criteria row for the single value field, using criteria that is appropriate for whatever type of data the values represent.

  5. Each value in the multivalued field will be individually evaluated using the criteria you supply. For example, you may have a multivalued field that stores a list of numbers. If you supply the criteria >5 AND <3, any record where there is at least one value greater than 5 and one value less than 3 will match.