Using multiple criteria

Often one criterion is not enough. You may want to prune down the records displayed by using multiple criteria for a single field or multiple criteria for different fields. To get the data you want, however, you do need to know how Access combines your criteria.

When you have criteria for only one field, decide whether you want to see records that meet all criteria (in which case, join the criteria with AND) or whether you want records that meet only one criterion (in which case, join the criteria with OR). Of course, you may have three or more criteria, and you can join them with both AND and OR.

To join criteria for a single field with AND, type them into the Criteria line of the grid with AND between them - like this:

shows you records with values less than five as well as those greater than sixty-five.

To join multiple criteria for one field with OR, use one of these methods:

• Type your expressions into the Criteria row, separating them with OR.
• Type the first expression into the Criteria row, and type subsequent expressions using the Or rows in the design grid.

Whichever approach you take, the result is the same - Access displays records in the datasheet that satisfy one or more of the criteria expressions.

When you have criteria for different fields, you join them with either the OR or the AND operator. The operator is implied in the way you put the criteria into 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 on each row are treated as though they are joined by AND, but each row's worth of criteria are treated as though they 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 Access 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 on only one row to display in the datasheet.