Microsoft Office Tutorials and References
In Depth Information
Limiting Records with Criteria Expressions
Access puts the text in quotes for you. The result of the query is all records
that have either CA or California in the State field.
You can find records with null values by using the Is Null criterion. If
you want all records except those with null values, use the Is Not Null
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, however, are less likely
to create criteria that Access doesn’t understand if you use those characters
Table 1-5 lists the types of elements you may include in a criteria expression,
as well as the character to use to make sure that 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
You can refer to dates or times by using any allowed format. December 25,
2013, 12/25/13, and 25-Dec-13 are all formats that Access recognizes.
You can use 12-hour (a.m./p.m.) or 24-hour time.
Year numbers between 0 and 29 are prefixed with 20. (If you enter the year
as , Access completes the year as 2020.) Year numbers between 30 and 99 20
are prefixed with 19. (If you enter as the year number, Access completes 45
the year as 1945.) You have the option of entering all four digits of the year,
of course, to make sure that you enter the year you want.
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.” Use operators in your criteria
expressions to tell Access about more-complex criteria.