About using criteria
This topic provides information about the following:
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 30">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.
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.
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.
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.
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).
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.
An AND condition that uses two instances of the same field in the Criteria pane.
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">>10/31/1998, Query converts it to #10/31/98#">>#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.
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.
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.
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