Understanding the Elements of Expressions

An expression is a statement of intent. If you want an action to occur after meeting a specific condition, your expression must specify that condition. To select records in a query that contains ZIP field values of 90000 or higher, for example, you use the expression

 ZIP >= 90000 

if the ZIP field has a numeric data type.

Arithmetic calculations are expressions also. If you need an ExtendedAmount field in a query, for example, use

 ExtendedAmount: Quantity * UnitPrice 

as the expression to create calculated values in the data cells of the ExtendedAmount column.

To qualify as an expression, a statement must have at least one operator and at least one literal, identifier, or function. In some cases, such as simple query criteria and field-validation rules, the equals operator (=) is inferred. The following list describes these elements:

• Operators include the familiar arithmetic symbols +, , * (multiply), and / (divide), as well as many other symbols and abbreviations. Some operators are specific to Access or SQL, such as the Between, In, Is, and Like operators.

• Literals consist of values that you type, such as 12345 or ABCDE. Literals are used most often to create default values and, in combination with field identifiers, to compare values in table fields and query columns.

• Identifiers are the names of objects in Access (such as forms and reports) or Jet (such as fields in tables) that return distinct numeric or text values. The term return, when used with expressions, means that the present value of the identifier is substituted for its name in the expression. For example, the field name identifier CompanyName in an expression returns the value (a firm name) of the CompanyName field for the currently selected record. Access has five predefined named constants that also serve as identifiers: True, False, Yes, No, and Null. Named constants and variables that you create in Access VBA also are identifiers.

• Functions return a value in place of the function name in the expression, such as the Date... and Format... functions, which are used in the examples in Chapter 9. Unlike identifiers, most functions require you to supply an identifier or value as an argument enclosed by parentheses. Later in this chapter, the "Functions" section explains functions and their arguments.

When literals, identifiers, or functions are used with operators, these combinations are called operands. The following sections explain these four elements of expressions more thoroughly.

Note

Expressions in this book appear in monospace type to distinguish expressions from the explanatory text. Operators, including symbolic operators, built-in functions, and other reserved words and symbols of VBA, are set in monospace bold type. (VBA reserved words appear in blue color in the Code-Editing window of modules.) SQL operators and names of Access objects are set in monospace type; by convention, SQL-92 reserved words are capitalized.