ms access where clause multiple

Multiple WHERE Clauses in MS Access

By Anthony Davis
AD

When working with Microsoft Access, the where clause is an essential tool for filtering records based on specific criteria. But what do you do when you need to apply multiple conditions to your query? The where clause in MS Access allows for multiple conditions, enabling you to filter data based on complex rules. Let's delve into how you can craft multiple conditions within a where clause in MS Access.

Before we dive in, it's crucial to understand the basic structure of the where clause. At its core, a where clause looks like this: WHERE field = condition. Here, field refers to the field you're filtering by, and condition is the value that record must match to be included in the result set.

Combining Multiple Conditions: AND, OR, and NOT Operators

MS Access offers three logical operators – AND, OR, and NOT – to combine multiple conditions within a where clause. These operators allow you to create complex queries, enabling you to filter data based on several criteria simultaneously.

AD
the 50 access shortcuts list is shown in red and white, with an image of
the 50 access shortcuts list is shown in red and white, with an image of
the microsoft access key is shown in this screenshote screen shot from windows xp
the microsoft access key is shown in this screenshote screen shot from windows xp
Tutorial - Creating a Contact Management Database (CRM) using Microsoft Access
Tutorial - Creating a Contact Management Database (CRM) using Microsoft Access

The AND operator requires that all conditions be true for a record to be included in the result set. In other words, a record must match every condition to be displayed. Conversely, the OR operator requires that only one condition be true for a record to be included. This means that a record will be displayed if it matches any of the conditions.

AND Operator

To combine multiple conditions using the AND operator, separate each condition with the word AND, like so:
WHERE field1 = condition1 AND field2 = condition2 AND field3 = condition3

MS Access Validation Rule Lookup Table Examples
MS Access Validation Rule Lookup Table Examples
Tutorial - Creating a Contact Management Database (CRM) using Microsoft Access
Tutorial - Creating a Contact Management Database (CRM) using Microsoft Access
an image of a computer screen with the names and numbers on it, in microsoft's office window
an image of a computer screen with the names and numbers on it, in microsoft's office window

Here, a record will only be included if it matches all of the conditions. For example, you might use this structure to filter a list of customers who live in both New York and California who are also over 30 years old.

OR Operator

The OR operator is used when you want to include records that match at least one of the conditions. To use the OR operator, separate each condition with the word OR, like so:
WHERE field1 = condition1 OR field2 = condition2 OR field3 = condition3

AD

In this case, a record will be included if it matches any of the conditions. For instance, you might use this structure to find customers who live in either New York or California who are also over 30 years old.

MS Access - Many-To-Many Relationship
MS Access - Many-To-Many Relationship
Verified: August 2026 ✅
Verified: August 2026 ✅
Microsoft Access Beginner Courses - Computer Learning Zone
Microsoft Access Beginner Courses - Computer Learning Zone
Microsoft Access Programmer - Get a Free Consultation and Quote for Your Database | Just Get Productive
Microsoft Access Programmer - Get a Free Consultation and Quote for Your Database | Just Get Productive
the quick reference card for microsoft access 2010 is shown in this screenshote image
the quick reference card for microsoft access 2010 is shown in this screenshote image
Unleash The Power of Microsoft Access
Unleash The Power of Microsoft Access

Excluding Records: The NOT Operator

The NOT operator, when used with a condition, serves to exclude records that match that condition. To use the NOT operator, place it before the condition, like so:
WHERE NOT field = condition

This will exclude any records that match the specified condition. For example, you might use this to exclude customers who live in California from your results.

AD

NOT Operator with AND and OR

It's also possible to combine the NOT operator with AND and OR operators to create even more complex queries. For instance, you might want to find customers who do not live in California and are over 30 years old. In this case, you would use the NOT operator with the AND operator like so:
WHERE NOT field = 'California' AND age > 30

Remember, in this case, a record will only be included if it does not match the condition for California and matches the condition for being over 30 years old.

Mastering these operators is key to making the most out of MS Access's where clause feature. By combining AND, OR, and NOT operators, you can craft sophisticated queries that filter data based on multiple, complex conditions. In turn, this can help you gain deeper insights from your data and make more informed decisions. So go ahead, start experimenting with these operators, and watch as your MS Access skills bloom!

AD
AD