| Lesson 10 | Using values in criteria expressions |
| Objective | Explain how to use the correct characters so that Access recognizes your criteria expressions. |
Using Values in Criteria Expressions
When you build criteria in a Microsoft Access 365 query, you are really writing a small expression that Access must interpret correctly. Most of the time, Access can infer what you mean. However, when expressions become longer or more complex, you need to use the correct delimiters (special characters) so that Access knows whether a value is text, a date/time, or a number—and whether a name represents a field or a literal value.
For example, suppose you want to display work you have done for Network Consultants, Inc.. Because the company name contains a comma and a period, Access needs to know that the entire phrase is one text value. In the Criteria row for the Company field, you would enter:
For example, suppose you want to display work you have done for Network Consultants, Inc.. Because the company name contains a comma and a period, Access needs to know that the entire phrase is one text value. In the Criteria row for the Company field, you would enter:
"Network Consultants, Inc."
The quotation marks tell Access, “this is text.” Without them, Access may interpret part of the string incorrectly or report an error.
Special Characters and Data Types
Access relies on specific characters to recognize each type of value in an expression. The table below summarizes the most common cases:
When Access recognizes the type of value in the expression, it may add these characters automatically in the query design grid. Learning the rules, however, helps you write criteria correctly—especially when you type expressions directly or switch to SQL View.
Special Characters |
|
| This type of data | Looks like this in an expression |
|---|---|
| Text | "Seattle" |
| Date | #1/1/2025# |
| Time | #1:00 AM# |
| Number | 10 |
| Field Name | [Company] |
When Access recognizes the type of value in the expression, it may add these characters automatically in the query design grid. Learning the rules, however, helps you write criteria correctly—especially when you type expressions directly or switch to SQL View.
Examples of Correctly Delimited Criteria
The following examples show how these characters work together in real criteria expressions:
-
Text with punctuation:
Access treats the entire value inside the quotes as one text string.="Network Consultants, Inc." -
Date range:
TheBetween #1/1/2024# And #12/31/2024##characters tell Access that the values are dates, not text. -
Time on or after 8:00 AM:
>= #8:00 AM# -
Numeric comparison:
No quotes or>= 100#symbols are used for pure numbers. -
Field name vs. literal text:
Square brackets mark[Company] = "ABC Webworks"[Company]as a field; quotes mark"ABC Webworks"as text. -
Parameter prompt:
When used in the Criteria row, brackets around a phrase that is not a field name create a parameter prompt.[Enter start date:]
Elements Found in Criteria Expressions
A complete criteria expression often combines several elements:
- Comparison operators (such as
=,>,<,BETWEEN,NOT) - Wildcard characters used with
Like(for example,Like "A*") - Date/time values wrapped in
#characters - Numeric values used without quotes
- Field names and parameters enclosed in square brackets
By combining these elements with the correct characters, Access can reliably interpret your criteria expressions. If an expression does not work as expected, check:
- Are text values enclosed in double quotes?
- Are all date/time values enclosed in
#characters? - Are field names or parameter prompts surrounded by square brackets?
- Are numbers written without quotes?