Introduction

The FieldName is the name of the field on which to apply the filtering. The Operator factor is a Boolean operator. Depending on the operator and your intentions, the Value can be one of the values in the FieldName or it can be a value not necessarily in the field.

There are various ways you can visually use the WHERE operator:

• You can use Filter By Form on a table, a query, or a form. After opening the list, click the combo box under a column and select the desired value
• You can use a grid. Use the Advanced Filter/Sort option from the Advanced button of the Ribbon. Select the column you want to use. In the Criteria box that corresponds to that column, enter the desired value

Besides using WHERE in data analysis, we have already seen that if you want to save the results of data analysis, you should store them in a query. We also saw that you could create a SQL expression and use it as the Record Source of a form or a report. Therefore, after opening a query in Design View or the Query Builder for a form or report, select the desired column(s). In the bottom section, click the Criteria box of the column that holds the values and type the desired value.

Here is an example: 

SELECT  Properties.[Property #], 
	Properties.[Date Listed], 
	Properties.[Property Type], 
	Properties.Condition, 
	Properties.City, 
	Properties.State, 
	Properties.Bedrooms, 
	Properties.Bathrooms, 
	Properties.[Market Value]
FROM 	Properties
WHERE 	(((Properties.[Property Type])="Single Family"));

When creating a query that creates a list of records based on a particular value, the presence of the column that holds that value may become redundant. For example, if you create a list of real estate properties that includes only single family homes, and if it is evident to the user that the list includes only those properties, there is no point to still include that column in the view. This means that the column that holds the WHERE condition can be left out of the expression displaying it. This technique also applies to the other SQL operators we will study in this and the next lessons.

To visually remove the column that holds a condition, in the Design View of the query, you can clear its Show check box.

SELECT 	Properties.[Property #], 
	Properties.[Date Listed], 
	Properties.Condition, 
	Properties.City, 
	Properties.State, 
	Properties.Bedrooms, 
	Properties.Bathrooms, 
	Properties.[Market Value]
FROM 	Properties
WHERE 	(((Properties.[Property Type])="Single Family"));