Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 36 ยท Queries

Criteria and Expressions in Access Queries

Filter Access queries with criteria: comparison operators, And/Or rows, Like wildcards, Between, In, Is Null, date literals, calculated fields, IIf, Nz and Format.

Criteria are the questions you ask of your data: "only UK customers", "price between 10 and 50", "orders placed in March", "names that start with S". Expressions go a step further and calculate new values: a line total, a full name, a status label. Together they give queries their power.

Access has its own expression language, close to Visual Basic. Some of it differs from standard SQL, so each idea below shows both the Access form and a runnable standard-SQL equivalent. The Access-only syntax appears in read-only snippets with a note on how to translate it.

Where criteria go

In the query grid, type a condition under the field in the Criteria row. The conditions in a single row are all required (And). To say "or", put another condition on the or row below. Access combines rows with Or:

Field Criteria or
Country "UK" "Norway"
UnitPrice >20

This asks for rows where Country is UK, or Country is Norway. The UnitPrice criterion appears only on the first row, so it applies only to the UK branch: (Country = "UK" And UnitPrice > 20) Or (Country = "Norway"). When mixing And and Or across rows, repeat shared conditions in every row.

Comparison operators

Operator Meaning Example
= Equals (the = is optional in the grid) "UK"
<> Not equal <>"UK"
<, <=, >, >= Less or greater >=20
Between ... And ... Inclusive range Between 10 And 50
In (...) Matches any in a list In ("UK","Norway")
Not Negates Not "UK"
Like Pattern match Like "S*"
Is Null / Is Not Null Empty or filled Is Null

Text values go in quotes. Numbers do not. Dates go between # signs.

SQL Comparisons, Between and In

Press Run (or Ctrl + Enter) to see the result here.

Wildcards with Like

Like matches patterns. In Access (the default ANSI-89 mode):

Wildcard Matches Example
* Any number of characters Like "S*" finds Sticky notes
? Exactly one character Like "?at" finds Cat, Hat
# One digit Like "A###" finds A123
[abc] One of the listed characters Like "[CB]at"
[a-f] One character in a range Like "[a-f]*"
[!abc] Any character not listed Like "[!A]*"

When a query runs through ADO or against a SQL Server link, or when a database uses ANSI-92 query mode (File > Options > Object Designers), the wildcards become % and _, like standard SQL. If a Like query returns nothing, check the mode.

The runnable engine uses the standard-SQL wildcards % (any run) and _ (one character):

SQL Like patterns (standard SQL wildcards)

Press Run (or Ctrl + Enter) to see the result here.

Null and empty values

A Null means "no value". = Null never matches, because comparing to an unknown is unknown. Use Is Null and Is Not Null.

For text fields, remember a zero-length string ("") is not Null. To find fields that are blank either way, use:

Text
Is Null Or ""
SQL Finding missing values

Press Run (or Ctrl + Enter) to see the result here.

Dates in criteria

Date criteria in Access use # delimiters and US-style mm/dd/yyyy order unless you write the safe form #yyyy-mm-dd#:

Text
>=#2026-03-01# And <#2026-04-01#

Dates you can use in the grid without typing literals:

  • Date() is today's date.
  • Now() is the current date and time.
  • >Date()-30 means within the last 30 days.
  • Between #1/1/2026# And #12/31/2026#.

Because a Date/Time field may carry a time of day, = #2026-03-05# matches only midnight. For a whole day, use >=#2026-03-05# And <#2026-03-06#, the "half-open range" pattern.

You can also filter on date parts with functions: Year([OrderDate]) = 2026, or Month([OrderDate]) = 3. That is easier to read but cannot use an index, so it is slower on huge tables.

SQL A whole day with a half-open range

Press Run (or Ctrl + Enter) to see the result here.

Calculated fields

A calculated field is a column built from an expression. In the Field row, type a name, a colon, then the expression:

Text
LineTotal: [Quantity] * [UnitPrice]
FullName: [FirstName] & " " & [LastName]

Notes on Access expressions:

  • & joins text; + can also join text but propagates Null, while & treats Null as empty.
  • Field names go in square brackets.
  • Standard arithmetic: + - * / ^ plus \ (integer division) and Mod.

Standard SQL uses || to join text and AS for names:

SQL Calculated columns

Press Run (or Ctrl + Enter) to see the result here.

The second query shows a pitfall: in standard SQL || with a Null gives Null (so Leo's full name is blank). Access's & would give Leo , while Access + would give Null. Know which operator you use.

Useful Access functions

Function Purpose Example
IIf(test, a, b) If-then-else IIf([Stock]=0,"Out","In stock")
Nz(value, default) Replace Null Nz([Discount],0)
Format(value, fmt) Format numbers and dates Format([OrderDate],"yyyy-mm")
Left, Right, Mid Slice text Left([Name],3)
Len, Trim, UCase, LCase Text helpers UCase([City])
InStr Find text position InStr([Email],"@")
DateAdd, DateDiff, DatePart Date arithmetic DateDiff("d",[OrderDate],Date())
Round, Int, Abs Number helpers Round([Price]*1.2,2)
Switch Multi-branch choice Switch([Qty]>100,"Bulk",[Qty]>10,"Mid",True,"Small")
DLookup, DCount, DSum Domain aggregate DCount("*","Orders")

None of the Access function names exist in SQLite, which is why they appear in read-only form. This stand-in runs the same logic with CASE, COALESCE, strftime and julianday:

SQL Stand-ins for IIf, Nz, Format and DateDiff

Press Run (or Ctrl + Enter) to see the result here.

Parameters and references in criteria

You can refer to another field in a criterion (>[Stock]) or prompt the user with text in brackets ([Enter a category]). Prompts are covered in the parameter queries lesson.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A grid has Country = "UK" on the Criteria row and Country = "Norway" on the or row. What does it return?

    Separate rows are combined with Or, so rows matching either condition are returned.
  2. 2. Which criterion finds fields that have no value?

    Comparing with = Null never matches. Use Is Null, and add Or "" to catch zero-length text.
  3. 3. Which criterion selects all of March 2026 safely, even if dates carry times?

    A half-open range includes times on the last day. Between ending at March 31 midnight would miss afternoon times that day.
  4. 4. What does the & operator do with a Null in an Access expression?

    & is forgiving with Null; the + operator would return Null.

Summary

  • Criteria in one row are joined by And; separate rows are joined by Or.
  • Use Between, In, Like with * and ?, and Is Null for empties; dates take # delimiters.
  • Use half-open date ranges for whole days.
  • Calculated fields are written Name: expression, with fields in square brackets.
  • IIf, Nz, Format, DateDiff and friends are Access-specific, so translate them to CASE, COALESCE and date functions when moving to other databases.

Next: Joins in Access Queries