Microsoft Office Tutorials and References
In Depth Information
Limiting Records with Criteria Expressions
Whichever approach you take, the result is the same: Access displays
records that satisfy one or more of the criteria expressions.
When you have criteria for different fields, you join them with the OR or
AND operator. The operator is implied in the way you put the criteria in the
design grid. Here’s how that works:
✦ Criteria on the same row are implicitly joined by AND. Access assumes
that you want to find records that meet all the criteria. If you type
criteria on the same row for two fields, a record has to meet both criteria to
be displayed in the datasheet.
✦ Criteria on different rows are joined by OR. Access assumes that you
want to find records that meet at least one criterion. If you type criteria
on different rows for two fields, a record has to meet only one criterion
to be displayed in the datasheet.
✦ When you use multiple rows for criteria, the expressions within each
row are treated as though they’re joined by AND, but each row’s worth
of criteria are joined by OR. Access first looks at one row of criteria
and finds all the records that meet all the criteria on that row. Then it
starts over with the next row of criteria — the Or row — and finds all the
records that meet all the criteria on that row. The datasheet displays all
the records that are found. A record has to meet all the criteria in only
one row to display in the datasheet.
Using lookup fields in criteria
When you define a criterion for a query, tell Access what you’re looking
for by entering a value or using a logical expression. If you use a criterion
to limit the number of records displayed from a lookup field, however, you
have to figure out exactly what value you want to find — which may not be
the value you see in the table. See Book II, Chapter 5 for details on creating a
Suppose that you want to find orders for the Budget MP3 Player. The
Order Details table stores this data, as shown in Figure 1-9. Notice that
the ProductID field is a lookup field; it displays values from the Product
Name field of the Products table but stores the values from the Products
table’s primary key field, which is ProductID. The Products table is shown
in Figure 1-10.
Because the ProductID field in the Order Detail table is a lookup field, the
criteria need to refer to the value that is stored in the field, not the value
that displays. The value stored is the primary key field from the Products
table. The value that displays is the product name. If you enter Budget MP3
Player as the ProductID criterion and then try to view the datasheet, you
get a Data type Mismatch in Criteria Expression error message.