Working with Multiple Query Criteria in Access

In the previous lesson, you learned how to use parameter queries to make your queries flexible. Now you'll learn how to construct queries with multiple conditions using logical operators—a fundamental skill for extracting precisely the data you need from your database.

Multiple criteria queries allow you to answer complex business questions like "Show me all customers in Phoenix who haven't placed an order in 90 days" or "Find invoices over $1,000 that are either past due or from priority clients." Access provides two primary logical operators for combining conditions: AND and OR.

The AND operator requires all specified conditions to be true for a record to appear in your results. This creates a narrower, more restrictive filter because every condition must be satisfied simultaneously.

Common uses for AND:

• Finding records within a specific range (date between X and Y)
• Filtering by multiple attributes (city AND state AND status)
• Combining category filters with value thresholds

In Access Query Design view, you create AND conditions by placing criteria horizontally across the same Criteria row for different fields. Each criterion on the same row must be true for the record to be included.

Example: Finding clients whose last name starts with "C" AND who live in Phoenix:

The table below shows how Access evaluates two conditions combined with AND. Notice that the result is True only when both conditions are True:

The OR operator requires at least one condition to be true for a record to appear in your results. This creates a broader, more inclusive filter because a record qualifies if it meets any of the specified conditions.

Common uses for OR:

• Searching for records matching any of several values
• Including multiple categories or status types
• Creating alternative filter paths

In Access Query Design view, you create OR conditions by placing criteria vertically in different rows (using the "or:" row and additional rows below). Any criterion being true in any row qualifies the record for inclusion.

Example: Finding clients whose last name starts with "C" OR who live in Phoenix:

The table below shows how Access evaluates two conditions combined with OR. Notice that the result is True when either or both conditions are True:

You can combine AND and OR operators to handle complex filtering requirements. When mixing operators, remember that Access evaluates criteria on the same row with AND logic, and criteria on different rows with OR logic.

Example business scenario: "Find all clients in Phoenix with last names starting with C, or all clients in Tucson regardless of last name."

This would be structured as:

• Row 1: City = "Phoenix" AND LastName Like "C*"
• Row 2: City = "Tucson"

Access first evaluates each row independently (applying AND within each row), then combines the results using OR logic across rows.

Modern versions of Access (Access 365 and Access 2019+) include helpful features for working with complex criteria:

• IntelliSense support: Auto-completion for field names and functions helps avoid syntax errors
• Expression Builder improvements: Enhanced interface for constructing complex criteria
• Zoom box (Shift+F2): Opens a larger editing window for complex criteria expressions
• SQL View integration: Toggle between Design View and SQL View to understand the generated SQL WHERE clause

Tip: Use the Zoom box when working with long or complex criteria. Press Shift+F2 while in any Criteria cell to open a larger editing window with better visibility.

1. Test incrementally: Start with one criterion, verify results, then add additional criteria
2. Use parentheses in SQL View: When logic becomes complex, switch to SQL View and use parentheses to explicitly control evaluation order
3. Document complex queries: Add comments or notes explaining the business logic behind intricate criteria combinations
4. Check for null values: Remember that null values may not behave as expected in logical comparisons—use Is Null or Is Not Null explicitly when needed
5. Review the datasheet: Always examine your results to confirm the query returns the expected records

Now that you understand how to filter data with multiple criteria using AND and OR operators, the next lesson will show you how to set field properties in queries to control data formatting, validation, and display characteristics. These properties give you fine-grained control over how query results appear and behave.

Test your understanding of filtering with multiple criteria, parameter queries, and logical operators.

Filtering Multiple Criteria - Quiz

Practice creating queries with AND clauses by working through a hands-on exercise with the sample database.

Multiple Query Criteria - Exercise