Note The information in this topic applies only to a Microsoft Access database (.mdb).
You can use expressions (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) to specify criteria that identify the records you want to work with in:
In queries, you can also use expressions to update values in a group of records or to create calculated fields (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.).
You can see how Microsoft Access evaluates query expressions by switching to a query's SQL view to display its underlying SQL statements. If your query includes linked tables, the values you specify in criteria on fields from the linked tables are case-sensitive (case-sensitive: Capable of distinguishing between uppercase and lowercase letters. A case-sensitive search finds only text that is an exact match of uppercase and lowercase letters.)— they must match the case of the values in the underlying table.
Where to enter expressions
In the design grid, you enter the expression in different places depending on the operation you're trying to perform. After you type an expression in the design grid and press the ENTER key, Access displays the expression using syntax it understands. If you don't include an operator (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.) with the expression, Access assumes you want the = operator. For example, if you type Denmark as the criteria in a Country field, Access displays "Denmark", and interprets the expression as Country="Denmark". If you include references to field names in the expression, the fields must be in one of the tables added to the query (unless you are using the DLookup function or a subquery). You can't drag the fields to the expression in the design grid, however. The following table shows where you can enter expressions.
| To do this |
Type the expression in |
| Specify criteria for a select (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.), crosstab (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.), or action query (action query: A query that copies or changes data. Action queries include append, delete, make-table, and update queries. They are identified by an exclamation point (!) next to their names in the Database window.), or for an advanced filter |
A Criteria cell in the design grid of a query or advanced filter. |
| Create a calculated field |
A Field cell in the design grid of a query or advanced filter. |
| Update records according to the results of the expression (update queries (update query: An action query (SQL statement) that changes a set of records according to criteria (search conditions) that you specify.) only) |
An Update To cell in the design grid of an update query. |
| Specify the records you want to work with, how they are grouped, the criteria for the groupings, or how records are sorted |
An SQL statement in SQL view. |
| Specify criteria when using Filter By Form |
A field in the Filter By Form window for a custom expression, or select an expression from the list in the field. |
Canada
Printer-Friendly Version
Share
Hide All
