Access 2016 Intermediate

Access 2016

Lesson 3 – Using Operators in Queries

Run the query. Notice that only records that match the And condition appear in the recordset.

Practice the Concept: Return to Design view and delete the criteria. To find all records of customers with contract dates on or after 1/1/2011 and sales to date of $4,000 or more, type >=1/1/2011 in the first the Contract Date field Criteria row and >=4000 in the Sales to Date field Criteria row. Run the query. Notice that only records that match both And conditions appear in the recordset.

Return to Design view and delete the criteria.

U SING AN O R C ONDITION



Discussion

There are times you may want to find records that meet only one of several specified conditions. This is called an Or condition. If you want to find all customers in PA or all customers with sales to date of over $10,000, for example, you need two conditions: State=PA and Sales to Date>10000. A record needs to meet only one of the conditions to be included in the recordset. Create an Or condition in a single field by entering criteria in different the field’s Criteria rows. You can also create an Or condition by typing criteria in different two or more field’s Criteria rows. For example, to find all customers with contract dates on or before January 1, 2013 or all those customers with credit limits above $3,000, type <=1/1/2013 in the Contract Date field Criteria row and >3000 in the Credit Limit field or row.

Create additional Or and And conditions by typing criteria into the Criteria row, the or row, or any row below the or row. Any criteria entered into the same Criteria row creates an And condition, any criteria entered into different rows creates an Or condition, and you can even create a combination of And and Or conditions.

NOTE Criteria entered in the same Criteria row across the design grid create an And condition, and criteria entered in different Criteria rows create an Or condition. Use the Expression Builder by selecting the Criteria row and, on the Ribbon in the Design tab Query Setup group, click the Builder button.

OFFICEPRO, Inc.

Page 57