About Expressions in Queries

Overview of expressions

You can use an expression anywhere in a query where you can use a column name. Expressions can calculate values to display, be part of search conditions, or combine the contents of data columns. An expression can consist of a mathematical calculation or a string, and can involve any combination of column names, literals, operators, or functions.

Examples of the use of expressions in a query include:

In a products table, displaying a discounted price that is calculated by taking 10% off the retail price.
Displaying only the first three digits — the area code — of a phone number.
Displaying employee names in the format last_name, first_name.
Joining two tables, an orders table and a products table, then sorting the query on the total price (order quantity times product price).
In an orders table, copying and then deleting all orders that were shipped more than one year ago.

Expression structure and expression examples

General guidelines for creating expressions

An expression consists of any combination of column names, literals, operators, or functions. Follow these guidelines in combining elements to form expressions:

Reference columns by typing their names. If your query uses more than one table and if you use a column name that is not unique, you must add the table name and a period to the column name. The following example shows the column name job_id qualified with the table name employee:
```
employee.job_id
```
Include literal text by enclosing it in single quotation marks; no quotation marks are necessary for numbers.
Note The Query Designer can accept terms in double quotation marks, but might interpret them differently than you expect. In a Microsoft Access Project, the Query Designer always interprets double quotation marks as database object delimiters.
Use standard arithmetic operators for numbers and a concatenation operator for combining strings.
Include parentheses to establish precedence of operators.
If you include a function, use these same guidelines for the arguments passed to the function. That is, reference columns by typing their names, enclose literal text in single quotation marks, and so on.
If you pass column names as function arguments, be sure the data type of the column is appropriate for the function argument.
You can include user-defined functions returning a scalar value in an expression.

Expression examples

The following table illustrates the use of expressions in a query.

Expression	Result
`SELECT (price * .9) FROM products`	Displays a discounted price (10% off the value in the price column).
`SELECT (lname + ', ' + fname) FROM employee`	Displays the concatenated values of the last name and first name columns with a comma between them.
`SELECT sales.qty, titles.price` `FROM sales INNER JOIN titles ON sales.title_id = titles.title_id ORDER BY (sales.qty * titles.price)`	After joining two tables, sorts the result set by the total value of an order (quantity times price).
`SELECT au_lname, au_fname FROM authors WHERE (SUBSTRING(phone, 1, 3) = '415')`	Displays authors whose area code is in the San Francisco area.
`SELECT ord_num, ord_date FROM sales WHERE (ord_date >= DATEADD(day, -10, GETDATE()))`	Finds all orders in the `sales` table that were made in the last 10 days. TodayÆs date is returned by the GETDATE( ) function.

Operators for expressions

You can use a variety of operators in constructing expressions for your query, including mathematical and text operators.

Mathematical operators

The following table lists the mathematical operators you can use in constructing an expression.

Operator	Meaning
+, -	Unary positive, negative
+	Addition
-	Subtraction
*	Multiplication
/	Division

Note You can use additional operators available, such as the "%" operator to determine modulo or remainders in arithemtic operations. For more information about the modulo arithmetic operator, see the Microsoft SQL Server documentation.

If you use more than one mathematical operator in an expression, the Query Designer processes the expression according to the following operator precedence. To override the default precedence, use parentheses around the portion of the expression that is to be evaluated first. If more than one operator of the same level is included, the operators are evaluated left to right.

Unary + and -
* and /
+ and -

Text operator

You can perform one operation on text: concatenation or the linking together of strings. You can concatenate strings and perform other operations (such as removing extraneous spaces) using a single operator. To concatenate a string, you can use the "+" operator in the Grid pane.

Predefined variables for expressions

In addition to using column names, literals, operators, and functions in an expression, you can use predefined variables that have defined meanings or values. For example, you can use a predefined variable to display the user name for the current user or to search for data columns that contain no value (null).

The list below includes examples of predefined variables.

Predefined Variable Description Example

Predefined Variable	Description	Example
CURRENT_USER	The user name of the current user	`UPDATE accounts SET salesperson = CURRENT_USER WHERE region = 'NW'` Changes the `salesperson` column for all accounts in the northwest region to the name of the current user.
NULL	A null value, used in the search expressions IS NULL and IS NOT NULL	`SELECT emp_id, lname, fname, minit FROM employee WHERE minit IS NULL` Finds authors who have no middle initial. Note The keyword IS is a special operator for use with NULL.

CURRENT_USER

The user name of the current user

UPDATE accounts 
SET salesperson = CURRENT_USER
WHERE region = 'NW'

Changes the salesperson column for all accounts in the northwest region to the name of the current user.

NULL

A null value, used in the search expressions
IS NULL and
IS NOT NULL

SELECT emp_id, lname, fname, minit
FROM employee
WHERE minit IS NULL

Finds authors who have no middle initial.

Note The keyword IS is a special operator for use with NULL.