Other Logical Operators on Queries

Range Test: BETWEEN

If you have a logical range of values and you want to know if a certain value is contained in that range, you can use the BETWEEN operator. The BETWEEN operator is usually combined with AND to get a list of records between two values. The basic formula of this operator is:

Expression BETWEEN Start AND End

The Expression placeholder of our formula is usually the name of the column whose values you want to examine. The Start factor is the starting value of the range to consider. The End factor is the highest value to consider in the range. After this condition is executed, it produces the list of values between Start and End. As an example, imagine that, from a list of employees, you want to consider only records from EmployeeID = 4 to EmployeeID = 7. You would write the statement in the Criteria field as follows:

The corresponding SQL statement is:

SELECT 	Employees.EmployeeID, Employees.DateHired,
	Employees.FirstName, Employees.LastName
FROM Employees
WHERE (((Employees.EmployeeID) Between 4 And 7));

This would produce:

 

This or That: IN

If you have a series of records and want to find a record or a group of records among them, you can use the IN operator. For example, from our list of employees, imagine that you want to create a list of employees who either work at the corporate office or in public relations. If you are visually creating the list, in the Criteria field of the Department column, you can type

IN("Corporate", "Public Relations")

Here is an example:

The corresponding SQL statement is:

SELECT 	Employees.DateHired, Employees.FirstName,
	Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.Department) In ("Corporate","Public Relations")));

This would produce:

SQL provides many and many other operators that we have not reviewed here.

Practical Learning: Using the IN Operator in a Query

1. In the Queries section of the Database window, right-click Inventory of Properties and click Design View
2. To create a list of apartments and townhouses, in the Criteria box of PropertyType, type
   IN (Apartment, Townhouse) and press Enter
     
   
3. Switch the query to Datasheet View to see the result
4. To save the query, on the main menu, click File -> Save As...
5. Type Apartments and Townhouses as the name of the query and press Enter
6. Close the query