Use values in expressions

You can specify a value in an expression by using a literal value, a constant, a function, or an identifier:

• 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-94# are examples of literal values. Dates are enclosed in number signs (#), and strings in double (") quotation marks.
  
  
• 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 Visual Basic for Applications that you can use in Visual Basic procedures.
  
  
• 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.

  You can also create your own functions using Visual Basic.

• 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

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 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" " "

For more information on constants, click .

For more information on creating your own functions, click .

For more information on using identifiers in expressions, click .