PAKISTAN INTERNATIONAL SCHOOL JEDDAH – ENGLISH SECTION

Information & Communication Technology

Defining Criterion in MS Access
Criteria for Text

The following examples are for the CountryRegion field in a query that is based on a table that
stores contacts information.

to include records Use this

Query result
that... criterion
Exactly match a value, "China" Returns records where the CountryRegion field is set
such as China to China.
Do not match a value, Not "Mexico" Returns records where the CountryRegion field is set
such as Mexico to a country/region other than Mexico.
Begin with the specified Like U* Returns records for all countries/regions whose names
string, such as U start with "U", such as UK, USA, and so on.
NOTE When used in an expression, the asterisk (*)
represents any string of characters — it is also called
a wildcard character.
Do not begin with the Not Like U* Returns records for all countries/regions whose names
specified string, such as U start with a character other than "U".
Contain the specified Like "*Korea*" Returns records for all countries/regions that contain
string, such as Korea the string "Korea".
Do not contain the Not Like "*Korea*" Returns records for all countries/regions that do not
specified string, such as contain the string "Korea".
Korea
End with the specified Like "*ina" Returns records for all countries/regions whose names
string, such as "ina" end in "ina", such as China and Argentina.
Do not end with the Not Like "*ina" Returns records for all countries/regions that do not
specified string, such as end in "ina", such as China and Argentina.
"ina"

Page 1
 to include records
Use this criterion Query result
that...
Contain zero-length "" (a pair of quotes) Returns records where the field is set to a blank
strings value. For example, records of sales made to another
department might contain a blank value in the
CountryRegion field.
Do not contain zero- Not "" Returns records where the CountryRegion field has a
length strings nonblank value.
Match one of two "USA" Or "UK" Returns records for USA and UK.
values, such as USA
or UK
Contain one of the In("France", "China", Returns records for all countries/regions specified in
values in a list of "Germany", "Japan") the list.
values
Match a specific Like "Chi??" Returns records for countries/regions, such as China
pattern and Chile, whose names are five characters long and
the first three characters are "Chi".

Criteria for Number, Currency, and AutoNumber fields

The following examples are for the UnitPrice field in a query that is based on a table that stores
products information. The criterion is specified in the Criteria row of the field in the query design
grid.

To include records Use this criterion Query result

that...
Exactly match a value, 100 Returns records where the unit price of the product
such as 100 is $100.
Do not match a value, Not 1000 Returns records where the unit price of the product
such as 1000 is not $1000.

Page 2
To include records that... Use this criterion Query result

Contain a value smaller < 100 Returns records where the unit price is less than
than a value, such as 100 <= 100 $100 (<100). The second expression (<=100)
displays records where the unit price is less than or
equal to $100.

Contain a value larger >99.99 Returns records where the unit price is greater than
than a value, such as >=99.99 $99.99 (>99.99). The second expression displays
99.99 records where the unit price is greater than or
equal to $99.99.

Contain one of the two 20 or 25 Returns records where the unit price is either $20
values, such as 20 or 25 or $25.

Contain a value that falls >49.99 and <99.99 Returns records where the unit price is between
with a range of values -or- (but not including) $49.99 and $99.99.
Between 50 and 100

Contain a value that falls <50 or >100 Returns records where the unit price is not
outside a range between $50 and $100.

Contain one of many In(20, 25, 30) Returns records where the unit price is either $20,
specific values $25, or $30.

Contain a value that ends Like "*4.99" Returns records where the unit price ends with
with the specified digits "4.99", such as $4.99, $14.99, $24.99, and so on.

Criteria for Date/Time fields

The following examples are for the OrderDate field in a query based on a table that stores Orders
information. The criterion is specified in the Criteria row of the field in the query design grid.

Page 3
TO INCLUDE USE THIS QUERY RESULT
RECORDS THAT ... CRITERION

Exactly match a value, #2/2/2006# Returns records of transactions that took place on Feb 2,
such as 2/2/2006 2006. Remember to surround date values with the #
character so that Access can distinguish between date
values and text strings.

Do not match a value, Not #2/2/2006# Returns records of transactions that took place on a day
such as 2/2/2006 other than Feb 2, 2006.

Contain values that fall < #2/2/2006# Returns records of transactions that took place before Feb
before a certain date, 2, 2006.
such as 2/2/2006 To view transactions that took place on or before this date,
use the <= operator instead of the < operator.

Contain values that fall > #2/2/2006# Returns records of transactions that took place after Feb
after a certain date, 2, 2006.
such as 2/2/2006 To view transactions that took place on or after this date,
use the >= operator instead of the > operator.

Contain values that fall >#2/2/2006# Returns records where the transactions took place
within a date range and between Feb 2, 2006 and Feb 4, 2006.
<#2/4/2006# You can also use the Between operator to filter for a
range of values, including the end points. For example,
Between #2/2/2006# and #2/4/2006# is the same as
>=#2/2/2006# and <=#2/4/2006# .

Contain values that fall <#2/2/2006# or Returns records where the transactions took place before
outside a range >#2/4/2006# Feb 2, 2006 or after Feb 4, 2006.

Contain one of two #2/2/2006# or Returns records of transactions that took place on either
values, such as #2/3/2006# Feb 2, 2006 or Feb 3, 2006.
2/2/2006 or 2/3/2006

Page 4