Page 5 of 23PREVNEXT

Queries II: Limit your data and calculate values

Type Example Description
Text "administrator" Finds all employees whose title is "administrator"
Number 03 Finds all courses whose section number is 3
Date #04/03/06# Finds all dates that exactly match 04/03/06
Expression with a comparison operator <Now() Uses the date function called Now() to retrieve all dates before today
Expression with a calculation ([UnitsReceived])<([UnitsOrdered]-3) Uses a calculation in criteria
Examples of criteria.

You can use different types of data, and even calculations or comparisons, in your criteria. Here are some common examples:

Text:     Surround specific words or phrases with quotation marks. You may notice that Access automatically applies quotation marks to your text criteria when you run the query, but the automatic placement of quotation marks may not be correct with complex criteria that contain multiple words or periods — for example:

"New York, N.Y." or "Sao Paulo"

Numbers:     You can use numbers and calculations in criteria. Do not surround numbers with quotation marks when the data is stored in a numeric field — one that only contains numbers. Do surround numbers with quotation marks, however, when a number is stored in a text field — for example, as part of an address.

Dates:     You can compare data to dates in a variety of ways. Surround dates with number signs (#). You may notice that Access automatically applies number signs to dates with certain formats when you run a query, but you should verify that number signs are placed correctly, in case you use a date format that Access doesn't recognize.

Expressions with comparisons and calculations:     You can use a combination of text, numbers, dates, and functions with comparisons and calculations. You'll learn about expressions in the next few sections.

Note     If you are retrieving numbers or dates and you don't get the results you want, you may want to verify the data type of the field. For example, some numbers might be treated as text, as in an address, and certain expressions (such as greater than) would work differently. You can determine the data type by viewing the table that contains the field in Design view.

Page 5 of 23PREVNEXT