About using criteria

Microsoft Query

Show All Show All

About using criteria

This topic provides information about the following:

What are criteria?

Meeting a single condition

Meeting multiple conditions

Finding similar records

Specifying a range of values

Using a calculation

Excluding specific records

Finding incomplete records

What are criteria?

Criteria are conditions you specify to find particular records in a database or list. By using criteria, you limit the records (or rows) that are included in the result set of a query. For example, the criteria UnitPrice > 30 selects records where the value in the UnitPrice field is greater than 30. You add criteria to the Criteria pane in Microsoft Query either by entering the criteria directly in the pane or by using Add Criteria on the Criteria menu. Fields you use to specify criteria do not have to be included in the result set.

Criteria pane

Because your query may contain multiple tables with similar field names, make sure that you use the field from the correct table. For example, if you want to find all of the suppliers for a particular country, select the Country field from the Supplier table instead of from the Customer table.

More information

Specify what records to retrieve by using criteria

Return to top

Meeting a single condition

You can find all records with values that meet a single condition— for example, a particular country or product. The following example shows criteria that retrieves all of the records that contain Sweden in the Country field.

Example of single criteria

To specify a single criteria, select the name of the field in the Criteria Field box, and then type the value you want to find in the Value box.

More information

Examples of expressions that use logical operators

Return to top

Meeting multiple conditions

Meet conditions in two or more fields You can find all records with values that meet one condition in one field and another condition in another field. For example, you can find all records for orders placed in the USA in the Washington (WA) region. To select these records, you would type two sets of criteria in the Criteria pane. To specify multiple conditions where all of the conditions are true, you create an AND condition.

Criteria that meets an AND condition

To find the records that meet two or more conditions, specify a criteria field for each condition.

Meet multiple conditions in the same field You can find all of the records with values that meet multiple conditions in the same field. For example, you can find all of the records for customers in both the USA and Mexico. To select these records, you would type two sets of criteria in the Criteria pane. To specify multiple conditions where one of the conditions is true, you create an OR condition. In an OR condition, only one of two (or more) conditions must be true.

 Criteria that meets an OR condition

The criteria above locates all customers from Mexico or the USA.

Note  You can also use the Or operator within a criteria value. For example, instead of typing Mexico in the or box you can type USA Or Mexico in the Value box.

More information

Examples of expressions that use logical operators

Return to top

Finding similar records

You can find records where the values in one field are similar, like phone numbers that have the same country or area code. For example, you can find all of the customers whose phone numbers begin with the area code (171) or the country codes (1) and (11).

Criteria that uses the Like operator

To use the Like operator, type the word Like, and then type the value you want to find within single quotation marks.

Note  The percent sign (%) in the example above is a wildcard character that represents any number of characters. You can use an underscore (_) when you want to represent just one character. The wildcard characters that you can use vary, depending on your data source. See the documentation for your data source driver for more information about the wildcard characters that the driver supports.

More information

Examples of expressions that use other types of operators

Return to top

Specifying a range of values

You can find all of the records that fall within a certain range of values or dates. For example, you can retrieve all records for orders placed between November 1, 1998, and December 31, 1998. To specify two or more conditions that must be met, you create an AND condition. You specify an AND condition to define a range of values.

Example of a range with two criteria

An AND condition that uses two instances of the same field in the Criteria pane.

A range that includes the And operator

An AND condition in which the lower and upper limits are separated by the And operator.

Note  When you type a date in the Criteria pane, Query converts the date to a format that it understands. For example, when you type the criteria >10/31/1998, Query converts it to >#10/31/98#.

More information

Examples of expressions that use comparison operators

Return to top

Using a calculation

You may need to locate records that match the result of a calculation, such as all records where the order total is greater than an amount that is based on the price field multiplied by the quantity field.

Example of calculated criteria

To find all of the records where the orders total more than $500, create a formula to calculate the order total, and then specify the criteria.

More information

Examples of expressions that use arithmetic operators

Return to top

Excluding specific records

Instead of locating records that match a specific value, you can find records that don't match a value. For example, you can use the Not operator to find all of the records for suppliers that are located outside of the United States.

Criteria that uses the Not operator

Use the Not operator to exclude records.

More information

Examples of expressions that use logical operators

Return to top

Finding incomplete records

You can find records that contain missing or incomplete values. For example, you can use the Is Null operator to find all of the records for employees who are missing a value for their supervisor's name.

Criteria that uses the Is Null operator

Use the Is Null operator to locate records with blank fields.

More information

Examples of expressions that use other types of operators

Return to top