dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

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.


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:

Projet hours

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:

Select query

Running the query shown above outputs all the 2,441 records for 2018:

Select query

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:

Query 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:

Select query

You’re not restricted in the number of ANDs you can apply. For example, Manufacturing Department’s involvement in Mars North:

Query Mars North Manufacturing

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:

Mars or Venus

With this result:

Mars or Venus

As an alternative to using Criteria rows to create logical OR conditions, you may create them within the same row:

Mars or Venus

With this result:

Mars or Venus


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.


“Anything that thinks logically can be fooled by something else that thinks at least as logically as it does.”

Douglas Adams, The Hitchhiker's Trilogy.