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