Logical AND and OR Conditions in Access Query Criteria
How to use various combinations of AND and OR logic to filter data using a Microsoft Access query.
Last updated on 2018-12-18 by David Wallis.
The Access query design grid offers almost limitless possibilities for filtering data. The application of AND and OR logic in the correct combination, and how this combination is represented in the grid, are key to your filter correctly delivering the set of data you’re seeking.
To illustrate the design of query filters we’ll use the example of a database that tracks people's project work, day-to-day:
Your business employs 750 people, each of whom works at one of five regional offices — North, South, East, West and Central — in one of the six departments — Admin, Design, Manufacturing, Testing, Sales and CRM — located in each of those offices.
The Unfiltered Query
This is the select query to which we’ll apply criteria to filter the data:
Running the query shown above outputs all the 2,441 records for 2018:
The records are sorted by date, starting with the earliest.
Applying Logical AND to Filters
Expressions linked by logical AND imply that those expressions are inclusive. So when you say to yourself that you want your query to filter all records relating to Project Mars in the North Region, you’re thinking Project Mars AND Region North. Or, to address your thinking as a logical expression, Project = Mars AND Region = North
In the Access query design grid, you apply filters by inputting into the Criteria part of the grid. By inputting into the same row of Criteria, you link the filters by logical AND. So here goes for Mars North:
When your criteria relate to text, you can type the quote marks or leave Access to put them in for you. 77 records for Mars North:
You’re not restricted in the number of ANDs you can apply. For example, Manufacturing Department’s involvement in Mars North:
I typed Man*, Access added the Like and quote marks.
You may use logical AND within a single Criteria expression. For example, you input this criterion against Person to filter for all people whose names begin with A as well as ending with N:
Like "A*" And Like "*N"
In this example Access doesn’t distinguish between upper and lower case. So Like "A*" And Like "*N" filters in exactly the same way as Like "a*" And Like "*n". Note also that Access decides to render the logical AND in the expression as And.
Applying Logical OR to Filters
A logical OR implies mutually exclusive criteria. Casually, you might be saying to yourself “I want records for Mars and Venus”. But a Project cannot be both Mars and Venus, it is either one or the other.
In your query design grid you may input your logical OR filters into different rows, like this:
With this result:
As an alternative to using Criteria rows to create logical OR conditions, you may create them within the same row:
With this result:
Give DMW’s Tips Your Support
ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.
Thanks, in anticipation.