MS-Access / Getting Started

---

Expression

Specifies a value in a predicate or in the select list of a SELECT statement or subquery.

Syntax

[+ | -] {function | [(]<expression>[)] | literal |
  column-name} [{+ | - | * | / | \ | ^ | MOD | &}
  {function | [(]<expression>[)] | literal |
  column-name}]...

Notes: function-You can specify one of the SQL aggregate functions: AVG, COUNT, MAX, MIN, STDEV, STDEVP, SUM, VAR, or VARP; however, you cannot use an SQL aggregate function more than once in an expression. In a desktop application (.accdb), you can also use any of the functions built into Access or any function you define using Microsoft Visual Basic. In a project file (.adp), you can use any of the SQL Server built-in functions.

[(]<expression>[)]-You can construct an expression from multiple expressions separated by operators. Use parentheses around expressions to clarify the evaluation order. (See the examples later in this section.)

literal-You can specify a numeric or an alphanumeric constant. You must enclose an alphanumeric constant in single quotation marks in a project file (.adp) or single or double quotation marks in a desktop database (.accdb). To include an apostrophe in an alphanumeric constant, enter the apostrophe character twice in the literal string; or, in a desktop database, you can also choose to enclose the literal string in double quotation marks. If the expression is numeric, you must use a numeric constant. In a desktop database (.accdb), enclose a date/time literal within pound (#) signs, and any date/time literal you enter in SQL view must follow the U.S. mm/dd/yy (or mm/dd/yyyy) format. This might be different from the format you use on the query design grid, which must follow the format defined for Short Date Style in your Regional And Language Options section of the Control Panel. In a project file (.adp), you must enclose date or time literals in single quotes, and you can use any specification inside the quotes that SQL Server can recognize as a date or time. For example, SQL Server recognizes any of the following as a valid date literal:

'April 15, 2011' '15 April, 2011' '110415' '04/15/2011' '2011-04-15'

column-name-You can specify the name of a column in a table or a query. You can use a column name only from a table or query that you've specified in the FROM clause of the statement. If the expression is arithmetic, you must use a column that contains numeric data. If the same column name appears in more than one of the tables or queries included in the query, you must fully qualify the name with the query name, table name, or correlation name, as in TableA.Column1. When a table or column name contains a blank or is a reserved word (such as select, table, name, or date) in a desktop database (.accdb), you must enclose each name in brackets, as in [Table A].[Column 1]. When a table or column name contains a blank or is a reserved word in a project file (.adp), you must enclose each name in double quotes, as in "Table A"."Column 1". Note that when you open a query in an Access project, Access includes the required SET QUOTED_IDENTIFIER ON command in the command string. However, if you execute an SQL Server query from a desktop database with a pass-through query, you must include this command in the pass-through query. Although in ANSI SQL (and SQL Server) you can reference an output-column-name anywhere within an expression, Access supports this only within the <field list> of a SELECT statement. Access does not support references to named expression columns in GROUP BY, HAVING, ORDER BY, or WHERE clauses. You must repeat the expression rather than use the column name. See SELECT Statement, for details about output-column-name.

+ | - | * | / | \ | ^ | MOD-You can combine multiple numeric expressions with arithmetic operators that specify a calculation. If you use arithmetic operators, all expressions within an expression must evaluate as numeric data types.

&-You can concatenate alphanumeric expressions by using the & operator in a desktop database (.accdb). In a project file (.adp), use + as the concatenation operator.

Examples

To specify the average of a column named COST, enter the following:

AVG(COST)

To specify one-half the value of a column named PRICE, enter the following:

(PRICE * .5)

To specify a literal for 3:00 P.M. on March 1, 2011, in a desktop database (.accdb), enter the following:

#3/1/2011 3:00PM#

To specify a literal for 3:00 P.M. on March 1, 2011, in a project file (.adp), enter the following:

'March 1, 2011 3:00PM'

To specify a character string that contains the name Marine Mail Order Company, enter the following:

'Marine Mail Order Company'

To specify a character string that contains a possessive noun (requiring an embedded apostrophe), enter the following:

'Andy''s Hardware Store'

or in a desktop database you can also enter:

"Andy's Hardware Store"

In a desktop database (.accdb), to specify a character string that is the concatenation of fields from a table named Customer List containing a person's first and last name with an intervening blank, enter the following:

[Customer List].[First Name] & " " & [Customer List].[Last Name]

In a project file (.adp), to specify a character string that is the concatenation of fields from a table named Customer List containing a person's first and last name with an intervening blank, enter the following:

"Customer List"."First Name" + ' ' + "Customer List"."Last Name"

See also Column-name, Predicates (BETWEEN, Comparison, EXISTS, IN, LIKE NULL, and Quantified), SELECT Statement, Subquery, and UPDATE Statement in this tutorial.