Logical AND and OR Conditions in Microsoft® Access Query Criteria

How to use various combinations of AND and OR logic to filter data using a Microsoft Access query.

Last updated on 2024-04-23.

Preface

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".

Applying Logical OR to Filters

A logical OR implies mutually exclusive criteria. Casually, you might be saying to yourself “I want Admin depatment's 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:

Issues With Ells and Ohs, Ones and Zeroes

Since I first posted this article I've been asked to address an issue relating to the construction of certain criteria. They're to do with distinguishing between letter O and numeral 0, and between letter l and numeral 1.

I plan to write an article on this. If you have an experience of this, then please let me know — contact@consultdmw.com — and perhaps I can addresss it in the planned article.

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.