About expressions in queries (ADP)

Microsoft Office Access 2003

previous page next page

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.

ShowExpression structure and expression examples

ShowGeneral 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.

ShowExpression 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.

ShowOperators for expressions

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

ShowMathematical 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.

  1. Unary + and -
  2. * and /
  3. + and -

ShowText 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.

ShowPredefined 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
CURRENT_USER The user name of the current user

Security  Use this feature with caution. Sensitive or confidential information could be revealed to other users.

 
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.
previous page start next page