MS-Access / Getting Started

---

Using dates, times, text, and values in criteria

Access does its best to recognize the types of data you use in criteria; it relies on its best guess when providing characters to enclose the elements of the criteria expressions you come up with. You are, however, less likely to create criteria that Access doesn't understand if you use those characters yourself.

Table-2 lists the types of elements you may include in a criteria expression - as well as the character to use to make sure Access knows the element is text, a date, a time, a number, or a field name.

Dates, Time, and Text in Criteria

Use This Type of Data... 	In an Expression Like This...
Text 				"text"
Date 				#1-Feb-97#
Time 				#12:00am#
Number 				10
Field name 			[field name]

You can refer to dates or times by using any allowed format. December 25, 2006, 12/25/06, and 25-Dec-06 are all formats that Access recognizes. You can use AM/PM or 24-hour time.

Using operators in criteria expressions

Don't be surprised if your criteria are frequently more complicated than "all records with California in the State field." You use operators in your criteria expressions to tell Access about more complex criteria.

Table-3 lists the operators that you're likely to use in an expression that specifies criteria.

Using Operators in Criteria

Relational Operator	What It Does
=	Finds values equal to text, a number, or date/time ("equal to" is understood when you type a criterion without an operator - you don't need to type it).
<>	Finds values not equal to text, a number, or date/time.
<	Finds values less than a given value.
<=	Finds values less than or equal to a given value.
>	Finds values greater than a given value.
>=	Finds values greater than or equal to a given value.
BETWEEN	Finds values between or equal to two values.
IN	Finds values or text included in a list.
LINE	Finds matches to a pattern.

When you type your criterion, you don't have to tell Access the field name. Just put your criterion in the same column as the field, and Access applies the criterion to the field that appears in the same column.

Table-3 explains how different criteria affect the records that appear onscreen in the query datasheet.

Examples of Criteria with Operators

When Field1 Has This Criteria	These Are the Records You See
<15	Displays records where Field1 is less than 15.
<#9/1/03#	Finds records where Field1 contains a date before September 1, 2003.
>15	Finds records where Field1 is greater than 15.
>#12:00am#	Finds records where Field1 is a time value after 12:00 a.m.
>[Max Price]	Finds records where Field1 is more than the value in the field Max Price.
<>15	Finds records where Field1 is not equal to 15.
>10 AND <20	Finds records where Field1 is between 11 and 19.
>=10 AND <=20	Finds records where Field1 is between 10 and 20, including 10 and 20.
BETWEEN 10 AND 20	The same as >=10 AND <=20.
IN ("Virginia", "VA")	Finds records where Field1 contains either Virginia or VA.
LIKE "A*"	Finds records where Field1 begins with the letter A. You can use LIKE with wildcards such as * to tell Access in general terms what you're looking for. For more information on the wildcards that Access recognizes.