Microsoft Office Tutorials and References
In Depth Information
Querying: The Basics
As Figure 4-7 shows, Access places double quotation marks (“”) around text
criteria and number signs (#) around date criteria. When you enter text or
date criteria, don’t enter the double quotation marks or number signs.
Access enters them for you.
When you need help writing an expression for a query, try clicking the
Builder button to construct your query in the Expression Builder dialog box.
This button is located on the (Query Tools) Design tab.
Entering numeric criteria
Enter numeric criteria in Number and Currency fields when you want to
isolate records with specific values. Earlier in this chapter, Table 4-1 describes
comparison operators you can use for querying and filtering. These
operators are invaluable when it comes to mining a database for information.
Use the greater than (>) and less than (<) operators to find values higher or
lower than a target value. Use the Between operator to find values between
two numbers. For example, Between 62 And 55 in a Currency field isolates
records with all items that sell for between $62.00 and $55.00.
Do not include commas in numbers when you enter them as criteria. For
example, enter 3200, not 3,200. Enter a comma and you get a “The
expression you entered contains invalid syntax. . .” error message.
Entering text criteria
To enter a text criterion, type it in the Criteria text box. For example, to find
students who attended Ohio State University, enter Ohio State in the Criteria
text box of the University field. Access places double quotation marks (“”)
around the text you enter as soon when you move the pointer out of the
Criteria text box.
Wildcards and the Not operator can come in very handy when entering text
✦ Wildcards: Wildcards make it possible to query for data whose
spelling you aren’t quite sure of. (In Chapter 3 of this mini-book, Table 3-2
explains what the wildcard characters are and how to use them.) For
example, entering Sm?th in the Criteria box of the Last Name field finds
all Smiths and Smyths. Entering E* in the Company field finds all
company names that begin with the letter E.
✦ Not operator: Use the Not operator to exclude records from query
results. For example, to exclude records with Belgium in the Shipped To
field, enter Not Belgium in the Criteria text box. This is a great way to
strip unneeded records from a query.