Specifying Criteria

Specifying Criteria
Criteria are restrictions we place on a query to identify the specific fields or records we
want to work with. For example, to view only the property number, city, type, and rent of all
properties in the PropertyForRent table, we construct the QBE grid shown in Figure 7.3(a).
When this select query is run, the retrieved data is displayed as a datasheet of the selected
fields of the PropertyForRent table, as shown in Figure 7.3(b). The equivalent SQL statement
for the QBE grid shown in Figure 7.3(a) is given in Figure 7.3(c).
Note that in Figure 7.3(a) we show the complete Select Query window with the target
table, namely PropertyForRent, displayed above the QBE grid. In some of the examples that
follow, we show only the QBE grid where the target table(s) can be easily inferred from
the fields displayed in the grid.
We can add additional criteria to the query shown in Figure 7.3(a) to view only properties
in Glasgow. To do this, we specify criteria that limits the results to records whose city field
contains the value ‘Glasgow’ by entering this value in the Criteria cell for the city field of the
QBE grid. We can enter additional criteria for the same field or different fields. When we
enter expressions in more than one Criteria cell, Microsoft Office Access combines them
using either:
n the And operator, if the expressions are in different cells in the same row, which means
only the records that meet the criteria in all the cells will be returned;
n the Or operator, if the expressions are in different rows of the design grid, which means
records that meet criteria in any of the cells will be returned.
For example, to view properties in Glasgow with a rent between £350 and £450, we enter
‘Glasgow’ into the Criteria cell of the city field and enter the expression ‘Between 350 And
450’ in the Criteria cell of the rent field. The construction of this QBE grid is shown in
Figure 7.4(a) and the resulting datasheet containing the records that satisfy the criteria is
shown in Figure 7.4(b). The equivalent SQL statement for the QBE grid is shown in
Figure 7.4(c).
Suppose that we now want to alter this query to also view all properties in Aberdeen.
We enter ‘Aberdeen’ into the or row below ‘Glasgow’ in the city field. The construction of
this QBE grid is shown in Figure 7.5(a) and the resulting datasheet containing the records
that satisfy the criteria is shown in Figure 7.5(b). The equivalent SQL statement for the
QBE grid is given in Figure 7.5(c). Note that in this case, the records retrieved by this
query satisfy the criteria ‘Glasgow’ in the city field And ‘Between 350 And 450’ in the rent
field Or alternatively only ‘Aberdeen’ in the city field.
We can use wildcard characters or the LIKE operator to specify a value we want to find
and we either know only part of the value or want to find values that start with a specific

Figure 7.3 (a) QBE grid to retrieve the propertyNo, city, type, and rent fields of the PropertyForRent table; (b) resulting datasheet; (c) equivalent SQL statement.

Figure 7.4 (a) QBE grid of select query to retrieve the properties in Glasgow with a rent between £350 and £450; (b) resulting datasheet; (c) equivalent SQL statement.

letter or match a certain pattern. For example, if we want to search for properties in Glasgow

but we are unsure of the exact spelling for ‘Glasgow’, we can enter ‘LIKE Glasgo’ into

the Criteria cell of the city field. Alternatively, we can use wildcard characters to perform

the same search. For example, if we were unsure about the number of characters in the

correct spelling of ‘Glasgow’, we could enter ‘Glasg*’ as the criteria. The wildcard (*)

specifies an unknown number of characters. On the other hand, if we did know the

number of characters in the correct spelling of ‘Glasgow’, we could enter ‘Glasg??’. The

wildcard (?) specifies a single unknown character.