# Refining a Query by Using Criteria

So far in this hour, you have learned how to select the fields you want and how to indicate the sort order for query output. One of the important features of queries is the ability to limit output by using selection criteria. Access allows you to combine criteria by using several operators to limit the criteria for multiple fields. Table 10.1 covers the operators and their meanings.

##### Table 10.1. Access Operators

Operator

Meaning

Example

Result of Example

=

Equal to

="Sales"

Finds only records with "Sales" as the field value.

<

Less than

<100

Finds all records with values less than 100 in that field.

<=

Less than or equal to

<=100

Finds all records with or equal tovalues less than or equal to 100 in that field.

>

Greater than or equal to

>100

Finds all records with values greater than 100 in that field.

>=

Greater than

>=100

Finds all records with or equal to values greater than or equal to 100 in that field.

<>

Not equal to

<>"Sales"

Finds all records with values other than Sales in the field.

And

Both conditions must be true

Created by adding criteria on the same line of the query design grid to more thanone field

Finds all records where the conditions in both fields are true.

Or

Either condition can be true

"CA" or "NY" or "UT"

Finds all records with the value "CA", "NY", or "UT" in the field.

Like

Compares a string expression to a pattern

Like "Sales*"

Finds all records with the value "Sales" at the beginning of the field (the asterisk is a wildcard character).

Between

Finds a range of values

Between 5 and 10

Finds all records with the values 5?10 (inclusive) in the field.

In

Same as Or

In("CA","NY","UT")

Finds all records with the value "CA", "NY", or "UT" in the field.

Not

Same as <>

Not "Sales"

Finds all records with values other than Sales in the field.

Is Null

Finds nulls

Is Null

Finds all records where no data has been entered in the field.

Is Not Null

Finds all records that are not null

Is Not Null

Finds all records where data has been entered into thefield.

Criteria entered for two fields on a single line of the query design grid are considered an And condition, which means that both conditions need to be true for the record to appear in the query output. Entries made on separate lines of the query design grid are considered an Or condition, which means that either condition can be true for Access to include the record in the query output. Take a look at the example in Figure 10.7; this query would output all records in which the ContactTitle field begins with either Marketing or Owner, regardless of the customer ID. It outputs the records in which the ContactTitle field begins with Sales only for the customers whose IDs begin with the letters M through R, inclusive. Notice that the word Sales is immediately followed by the asterisk. This means that salesman would be included in the output. On the other hand, Marketing and Owner are both followed by spaces. That means that only entries that begin with Marketing or Owner followed by a space are included in the output.

#### Working with Dates in Criteria

Access gives you significant power for adding date functions and expressions to query criteria. Using these criteria, you can find all records in a certain month, on a specific weekday, or between two dates. Table 10.2 lists the Data criteria expressions and examples.

##### Table 10.2. Date Criteria Expressions

Expression

Meaning

Example

Result

Date()

Current date

Date()

Records the current date within a field.

Day(Date)

The day of a date

Day([OrderDate])=1

Records the order date on the first day of the month.

Month(Date)

The month of a date

Month([OrderDate])=1

Records the order date in January.

Year(Date)

The year of a date

Year([OrderDate])=1991

Records the order date in 1991.

Weekday(Date)

The weekday of a date

Weekday([OrderDate])=2

Records the order date on a Monday.

Between Date And Date

A range of dates

Between #1/1/95# and #12/31/95#

Finds all records in 1995.

DatePart (Interval,Date)

A specific part of a date

DatePart ("q",[OrderDate])=2

Finds all records in the second quarter.

The Weekday(Date, [FirstDayOfWeek]) function works based on your locale and how your system defines the first day of the week. Weekday() used without the optional FirstDayOfWeek argument defaults to vbSunday as the first day. A value of 0 defaults FirstDayOfWeek to the system definition. Other values can be set also.

Figure 10.8 illustrates the use of a date function. Notice that DatePart("q",[OrderDate]) is entered as the expression, and the value 2 is entered for the criterion. Year([OrderDate)] is entered as another expression, with the number 1995 as the criterion. Therefore, this query outputs all records in which the order date is in the second quarter of 1995.

##### Figure 10.8. Using the DatePart() and Year() functions in a query.

 Design a query to find all the sales agents in Brazil or France. The criteria you build should look like those in Figure 10.9. The steps provide criteria for the contact title and country fields.

1. The criterion for the Country field should be "Brazil" Or "France" because you want both Brazil and France to appear in the query output. The criterion for the ContactTitle field should be "Sales Agent". Because you should enter the criteria for both the Country and ContactTitle fields on the same line of the query design grid, both must be true for the record to appear in the query output. In other words, the customer must be in either Brazil or France and must also be a sales agent.

2. Modify the query so that you can output all the customers for whom the contact title begins with Sales.

3. Try changing the criteria for the ContactTitle field to Sales. Notice that no records appear in the query output because no contact titles are just Sales. You must enter "Like Sales*" for the criteria. Now you get Sales Agent, Sales Associate, Sales Manager, and so on. You still don't see Assistant Sales Agent because their titles don't begin with Sales.

4. Try changing the criteria to "Like *Sales*". Now all Assistant Sales Agent appear. This is because the wildcard, the asterisk, appears both before and after Sales. This means that Access returns anything containing the word Sales anywhere within it. For full coverage of the asterisk wildcard, see Hour 3, "Queries Introduced."

##### Figure 10.9. The criterion to select sales agents whose country is either Brazil or France.

 Part I: Introduction to Relational Databases and Access 2003
 Part II: Working with Existing Databases and Objects
 Part IV: Power Access Techniques