Adding Criteria to Queries in Microsoft Access 365

In Microsoft Access 365, criteria allow you to filter your query results so that only records meeting specific conditions are returned. Criteria are written in the Criteria row of the query design grid and apply to a specific field. By using criteria effectively, you can display only the information you need—whether that means limiting results to one company, filtering a range of dates, or matching only certain types of values. This lesson explains how to apply, understand, and combine criteria in Query Design View.

Where Criteria Are Entered

Criteria are placed in the Criteria row directly beneath the field to which they apply. Each column in the design grid represents a field. To define criteria:

1. Open the query in Design View.
2. Identify the field you want to filter.
3. Type the criterion in the Criteria row under that field.

You may enter criteria for a single field, multiple fields, or multiple criteria for one field. The design grid interprets these combinations using logical AND and OR rules.

Filtering by Example (Query by Example)

One of the simplest ways to apply criteria is to type the exact value you want Access to match. This is often called Query by Example (QBE). For instance, if you want to limit your records to a specific company such as ABC Webworks, type that value into the Criteria row of the Company field.

Once criteria are applied, switch to Datasheet View to see the filtered results.

How Criteria Work in Access

Criteria act as filters that Access evaluates when producing the result set. When Access encounters a criterion, it checks whether each record matches that rule:

• If it matches → the record is displayed
• If it does not match → the record is excluded

Access supports criteria for text, numbers, dates, Boolean values, ranges, wildcards, and expressions. Combining criteria across fields allows precise control over which records appear.

Common Types of Criteria and Examples

1. Exact Match

Use an exact value to return only records equal to that value.

"Seattle"

2. Partial Match (Wildcards)

Use Like with wildcard symbols:

• Like "A*" → values starting with A
• Like "*web*" → values containing “web” anywhere

3. Numeric Comparisons

Use comparison operators:

• >50 — greater than 50
• <= 100 — less than or equal to 100
• BETWEEN 10 AND 20 — values from 10 through 20

4. Date Criteria

Date criteria must be enclosed in # signs:

• #1/1/2025#
• >#12/31/2024#
• Between #1/1/2024# And #12/31/2024#

5. Null or Non-Null Values

• Is Null — empty fields
• Is Not Null — fields that contain data

6. Multiple Criteria

AND logic goes on the same row:

>10 AND <50

OR logic goes on separate rows in the criteria grid.

Using the Expression Builder

For more complex filtering (nested expressions, functions, or parameters), Access provides an Expression Builder. It helps generate correctly formatted expressions without needing to memorize syntax.

Filtering by Multiple Fields

Criteria can be applied to several fields at once—for example, filtering by Company and Start Date simultaneously. Access combines these criteria based on their row placement:

• Same row → AND
• Different rows → OR

This gives you full flexibility for narrowing results to specific conditions.

Understanding Table Relationships in Criteria-Based Queries

Criteria often rely on correctly defined table relationships. For example, in a query that joins Clients, Projects, and Hours, criteria on the Company field will limit related project and hours data to matching records. Proper relationships ensure that filters behave as expected and prevent duplicate or missing rows.

Using Relational Operators

Here is a list of commonly used relational operators in Access criteria:

In the next lesson, you will learn how character expressions—such as text patterns and wildcard logic—can further refine your criteria.