About expressions

• To set a property that defines a calculated control, establishes a validation rule, or that sets a default field value.
• To enter a criteria expression, create a calculated field, or update records in a query or filter.
• To set a condition for carrying out an action or series of actions in a macro, or to specify arguments for many actions.
• To specify arguments for many functions, statements, and methods in Microsoft Visual Basic for Applications procedures.
• To edit an SQL query in SQL view of the Query window, or to use an SQL statement in a property setting or argument.

A literal value represents a value— such as a number, string, or date— that Microsoft Access evaluates exactly as written. "New York," 100, and #1-Jan-01# ('1-Jan-01' in ANSI-92) are examples of literal values.

Number signs (#) (or single (') quotation marks in ANSI-92) around an element of an expression indicate that the element is a date/time value. Microsoft Access automatically evaluates a value surrounded by number signs (or single quotation marks) as a date/time value and lets you type the value in any common date or time format.

You don't have to type number signs (or single quotation marks) around a date/time value in a validation expression or a criteria expression for a field whose data type is Date/Time. You can type the value in any common date or time format and Microsoft Access automatically inserts the correct signs around the value. Note that Access 2002 and later conforms to ANSI-92.

Microsoft Access displays the value according to the regional settings in Microsoft Windows Control Panel. You can change the output format of a date by using the Format property.

Double (") quotation marks around an element of an expression indicate that the element is text.

When you type text in a validation or criteria expression, you can type the text without double quotation marks and Microsoft Access will insert them automatically.

For example, if you type the expression Paris, Microsoft Access displays the expression as follows:

"Paris"

If you want an expression to produce a string that is enclosed in double quotation marks, you can either enclose the nested string in single (') quotation marks or three sets of double (") quotation marks. For example, the following expressions are equivalent:

Forms![Contacts]![City].DefaultValue = ' "Paris" '

Forms![Contacts]![City].DefaultValue = " " "Paris" " "

A constant represents a value that doesn't change. True, False, and Null are examples of constants that are defined automatically by Microsoft Access. You can also define your own constants in Microsoft Visual Basic for Applications that you can use in Visual Basic procedures.

Note  You cannot use Visual Basic constants in functions used in your expressions. For example, Visual Basic has constants for the days of the week that can be used in a function such as vbSunday to represent Sunday, vbMonday to represent Monday, and so forth. Each constant has a corresponding numeric value: the numeric value for vbSunday is 1 and the numeric value for vbMonday is 2. In a function used in an expression, you must use the numeric values to represent the particular day of the week.

A function returns a value based on the results of a calculation or other operation. Microsoft Access includes many built-in functions; for example:

• The Date function returns the current date.
• The Sum function returns the sum of a set of field values.
• The DLookup function returns a specific field value.

An identifier refers to the value of a field, control, or property. For example, the following identifier refers to the value of the DefaultValue property of the OrderDate control on the Orders form:

Forms![Orders]![OrderDate].DefaultValue

Brackets ([ ]) around a field, control, or property in an identifier indicate that the element is the name of a table, query, form, report, field, or control.

When you type an object name in an identifier, you surround the name with brackets if it contains a space or a special character, such as an underscore. If the name doesn't contain a space or a special character, you can type the name without brackets. Microsoft Access inserts brackets automatically (with two exceptions noted below).

For example, you can type the following expression as a setting for the ControlSource property to calculate the sum of the values in the Freight and OrderAmount fields:

= Freight + OrderAmount

Microsoft Access displays the expression as follows:

= [Freight] + [OrderAmount]

Note  In a ValidationRule property setting or in a Criteria cell in the query design grid, Microsoft Access doesn't always automatically insert brackets around a name. If you are entering an object name, be sure to type brackets around it. Otherwise, Microsoft Access may assume you are entering text and may insert double quotation marks.

You use the ! and . (dot) operators in an identifier to indicate the type of item that immediately follows.

The ! operator

The ! operator indicates that what follows is a user-defined item (an element of a collection). For example, use the ! operator to refer to an open form, an open report, or a control on an open form or report.

The . (dot) operator

The . (dot) operator usually indicates that what follows is an item defined by Microsoft Access. For example, use the . (dot) operator to refer to a property of a form, report, or control. You can also use the . (dot) operator to refer to a field value in an SQL statement, a Microsoft Visual Basic for Applications method, or a collection.

You can combine the value of a field, control, or property with a literal string by using the & (concatenation) operator. For example, the following expression combines the literal string "[CategoryID] = " with the value of the CategoryID control on the Products form:

"[CategoryID] = " & Forms![Products]![CategoryID]

In some circumstances— for example, in a domain aggregate function such as DLookup— the value of the field, control, or property must appear in single (') or double (") quotation marks. The easiest way to accomplish this is to add a single quotation mark to the literal string, and then combine the expression with another literal string made up of a single quotation mark after the field, control, or property value, as follows:

"[CategoryID] = ' " & Forms![Products]![CategoryID] & " ' "

Existing Microsoft Access applications may use the vertical bar operators (| |) in place of an opening and closing combination of double quotation marks and & (concatenation) operators, as follows:

"[CategoryID] = '|Forms![Products]![CategoryID]|' "

However, the use of vertical bars is not recommended because they can produce unexpected results in some circumstances.

If you are entering an expression that is longer than the standard input area in a property sheet, a design grid, or an action argument, you can type the expression in the Zoom box. To open the Zoom box, press SHIFT+F2 when the focus is where you want to enter the expression.

When you enter an expression in a property sheet, a design grid, or an action argument, Microsoft Access:

• Inserts certain characters for you when the focus changes. Depending on where you enter the expression, Microsoft Access automatically inserts brackets ([ ]) around form, report, field, or control names, number signs (#) around dates, and double (") quotation marks around text.

  Note  When you add an expression to a calculated control, you must add an equal sign (=) before the expression. Access does not insert the equal sign for you.

• Recognizes region-specific function names, property names, and list separators for international versions. When you enter a function or property in an expression in most international versions of Microsoft Access, you can:
  • Type its localized name (in your language) in a property sheet, a design grid, or an action argument.
  • Use the list separator for your country/region when you specify more than one argument for a function. You specify the list separator on the Number tab as part of the regional settings in Microsoft Windows Control Panel. For most international versions, the default list separator is a semicolon (;).

    However, in Microsoft Visual Basic for Applications code, you must type the English function or property name and use a comma (,) as a list separator.

The Expression Builder has three sections, located from top to bottom:

Expression box    In the upper section of the builder is an expression box where you build the expression. Use the lower section of the builder to create elements of the expression, and then paste these elements into the expression box to form an expression. You can also type parts of the expression directly into the expression box.

Operator buttons    In the middle section of the builder are buttons for commonly used operators. If you click one of the operator buttons, the Expression Builder inserts the operator at the insertion point in the expression box. For a complete list of operators you can use in expressions, click the Operators folder in the lower-left box and the appropriate operator category in the middle box. The right box lists all operators in the selected category.

Expression elements    In the lower section of the builder are three boxes:

• The left box contains folders that list the table, query, form, and report database objects, built-in and user-defined functions, constants, operators, and common expressions.
• The middle box lists specific elements or categories of elements for the folder selected in the left box. For example, if you click Built-In Functions in the left box, the middle box lists categories of Microsoft Access functions.
• The right box lists the values, if any, for the elements you select in the left and middle boxes. For example, if you click Built-In Functions in the left box and a category of functions in the middle box, the right box lists all built-in functions in the selected category.

Note  When you paste an identifier in your expression, the Expression Builder pastes only the parts of the identifier that are required in the current context. For example, if you start the Expression Builder from the property sheet of the Customers form, and then paste an identifier for the Visible property of the form in your expression, the Expression Builder pastes only the property name: Visible. If you use this expression outside the context of the form, you must include the full identifier: Forms![Customers].Visible