To filter records:

Type this:

To filter records:

Type this:

Exactly 1/1/2000
Exactly $10.00
Exactly M

1/1/2000
10
M

“And”

And

Before 1/1/2000
Less than $10.00
Data starting with A-L

<1/1/2000
<10
<M

Dates from 1/1/2000 to 12/31/2000

>=1/1/2000 And <=12/31/2000

1/1/2000 or before
$10.00 or less

<=1/1/2000
<=10

“Or”

Or
Alternatively, enter the criteria on two separate criteria rows

After 1/1/2000
More than $10.00

>1/1/2000
>10

Either 1/1/2000 or 1/1/2001

=1/1/2000 Or 1/1/2001

1/1/2000 or after
$10.00 or more
Data starting with M-Z

>=1/1/2000
>=10
>=M

Is either CA, CO, NY or PA

In(‘CA’,’CO’,’NY’,’PA’)

“Not”

Not

Empty / Not Empty

Is Null / Is Not Null

Not before 1/1/2000
Not less than $10.00

Not <1/1/2000
Not <10

Like – allows you to use “wildcards”

One character
One or more characters

Like

?
*

Not after 1/1/2000
Not more than $10.00

Not >1/1/2000
Not >10

Less than the reorder level

<\\[ReorderLevel\\]

Between $10.00 and $20.00

Between 10 And 20

   

Notes and Tips:

  • Access will often change your entry and add symbols so that the expression is specified with the correct syntax. Do not be concerned by this behavior.
  • Field names can be included in expressions. Simply surround the field name with square brackets, e.g. <\\[ReorderLevel\\] would evaluate to “less than the value stored in the ReorderLevel field of the same record.”
  • If there are two or more tables with the same field name, you must specify which field you mean by using the format: \\[tablename\\].\\[fieldname\\]. Better yet, avoid using duplicate field names unless the fields actually contain identical data (such as a primary key field name being used as the foreign key fieldname in a related table).
  • Enter a prompt surrounded by brackets and the user will be prompted for input when the query is run. For example, try the criteria: >=\\[Enter a starting date:\\]
  • To create more complex criteria, right-click the Criteria cell and choose Build.