Access SQL WHERE Clause

This Access tutorial explains how to use SQL WHERE clause to add criteria using AND OR keywords.

You may also want to read:

Access SQL SELECT, SELECT TOP, SELECT INTO

Access SQL WHERE Clause

In my previous post, I explained how to use SELECT statement to select desired fields. After selecting fields, the next step is to filter data.

If no record  in the table can match the criteria, no result will return.

Access SQL WHERE Clause with “AND” Criteria

Syntax

For single criteria

SELECT expressions
FROM tables

For more than 1 criteria

SELECT expressions
FROM tables
WHERE criteria1 AND criteri2

Example of  WHERE with “AND”

Assume that we have created the below table.

Employee_tbl
Employee ID Employee Name Date of Join
1 John 1/1/2013
2 Mary 2/3/2014
3 Peter 1/5/2015
4 Peter 7/5/2015

Create a Query in Design View. Modify the Query as below.

access_SQL_select_2

Note the following requirements of criteria for different data types

Field Data Type Remarks on criteria
Employee ID Number Use < and > operators for comparison
Employee Name Text “text” or ‘text’
Date of John Date #date#

 

Query Result
Employee ID Employee Name Date of Join
1 John 1/1/2013

Switch to SQL View and see what SQL has been generated

SELECT Employee_tbl.[Employee ID], Employee_tbl.[Employee Name], Employee_tbl.[Date of Join]
FROM Employee_tbl
WHERE (((Employee_tbl.[Employee ID])=1) AND ((Employee_tbl.[Employee Name])="John") AND ((Employee_tbl.[Date of Join])=#1/1/2013#));

You can see that the Criteria in Design View is converted to the WHERE clause in SQL. The three criteria are joined by AND keyword.

Access SQL WHERE Clause with “OR” Criteria

Switch to Design View and modify the Query as below.

access_SQL_select_3

Each row of Criteria represents a group of AND criteria. When you starting a new row of Criteria, you add a OR relationship.

In the example above, the criteria is

(Employee ID = 1 AND Employee Name = “John”) OR Employee ID = 2

Switch to SQL View to see what has been generated

SELECT Employee_tbl.[Employee ID], Employee_tbl.[Employee Name], Employee_tbl.[Date of Join]
FROM Employee_tbl
WHERE (((Employee_tbl.[Employee ID])=1) AND ((Employee_tbl.[Employee Name])="John")) OR (((Employee_tbl.[Employee ID])=2));

Outbound References

https://support.office.com/en-us/article/Access-SQL-WHERE-clause-753BBC13-DEBC-4B28-B527-42EB7885C862

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *