Chapter 5: Using Operators and Expressions in Access
193
around field names that contain spaces or punctuation characters. An example of a field name in
brackets is[tblSales].[SaleDate] + 30In this example, 30 days is added to the SaleDate field in tblSales.Caution
If you omit the brackets ([ ]) around a field name in the QBE grid, Access might place quotes around the field
name and treat it as literal text instead of a field name.
Entering Single-Value Field Criteria
You’ll encounter situations in which you want to limit the query records returned on the basis of a
single field criterion, such as in these queries:l (^) Customer (buyer) information for customers living in New York state
l Sales of truck models
l (^) Customers who bought anything in the month of January
Each of these queries requires a single-value criterion. Simply put, a single-value criterion is the
entry of only one expression in the QBE grid. The expression can be example data, such as “CA”,
or a function, such as DatePart(“m”,[SaleDate]) = 1. Criteria expressions can be specified
for virtually any data type: Text, Numeric, Date/Time, and so forth. Even OLE Object and Counter
field types can have criteria specified.
Entering character (Text or Memo) criteria
You use character criteria for Text or Memo data-type fields. These are either examples or patterns
of the contents of the field. To create a query that returns customers who live in New York state,
for example, follow these steps:
- Open a new query in Design view based on tblCustomers and add the FirstName,
LastName, and State fields to the QBE pane. - Click the Criteria cell for State field.
- Type NY in the cell.
Your query should look like Figure 5.5. Notice that only one table is open and only three
fields are selected. Click the Datasheet View button in the Home ribbon’s Views group to
see this query’s results.