Access and VBA provide six categories of operators that you can use to create expressions:
Operators in the first four categories are available in almost all programming languages. Identifier operators are specific to Access; the other operators of the last category are provided only in RDBMSs that create queries based on SQL. The following sections explain how to use each of the operators in these categories. Arithmetic OperatorsArithmetic operators operate only on numeric values and must have two numeric operands, with the following exceptions:
Table 10.1 lists the arithmetic operators that you can use in Access expressions.
Jet/VBA operators are identical to operators used by all versions of BASIC. If you aren't familiar with BASIC programming, the following operators need further explanation:
These three operators seldom are used in business applications but often occur in VBA program code.
Assignment and Comparison OperatorsTable 10.1 omits the equal sign associated with arithmetic expressions because in Access you use it in two ways neither of which falls under the arithmetic category. The most common use of the equal sign is as an assignment operator; = assigns the value of a single operand to an Access object or to a variable or constant. When you use the expression = "Q" to assign a default value to a field, the equal sign acts as an assignment operator. Otherwise, = is a comparison operator that determines whether one of two operands is equal to the other. Comparison operators compare the values of two operands and return logical values (True or False) depending on the relationship between the two operands and the operator. An exception is when one of the operands has the Null value. In this case, any comparison returns a value of Null. Because Null represents an unknown value, you cannot compare an unknown value with a known value and come to a valid True or False conclusion. Table 10.2 lists the comparison operators available in Access.
The principal uses of comparison operators are to create validation rules, to establish criteria for selecting records in queries, to determine actions taken by macros, to create joins using the SQL-89 WHERE and SQL-92 JOIN clauses, and to control program flow in VBA. Logical OperatorsLogical operators (also called Boolean operators) are used most often to combine the results of two or more comparison expressions into a single result. Logical operators can combine only expressions that return the logical values True, False, or Null. With the exception of Not, which is the logical equivalent of the unary minus, logical operators always require two operands. Table 10.3 lists the Jet/VBA logical operators.
The logical operators And, Or, and Not are used extensively in Access expressions and SQL statements; in SQL statements these operators are uppercase, as in AND, OR, and NOT. Xor is seldom used in Jet or VBA. Eqv (equivalent) and Imp (implication) are rarely seen, even in programming code, so Table 10.3 omits these two operators.
Concatenation OperatorsConcatenation operators combine two text values into a single string of characters. If you concatenate ABC with DEF, for example, the result is ABCDEF. The ampersand (&) is the preferred concatenation operator in VBA and Jet. Concatenation is one of the subjects of "The Variant Data Type in Jet and VBA" section later in the chapter. Tip Don't use the + symbol to concatenate strings in queries or Jet SQL. In Jet SQL and VBA, + is reserved for the addition of numbers; & concatenates literals and variables of any field data type. The & operator performs implicit type conversion from numbers to text; the & operator treats all variables as character strings. Thus 1234 & 5678 returns 12345678, not 6912.
Identifier OperatorsEarlier versions of Access used identifier operators, !(the exclamation point, often called the bang operator) and .(the period, called the dot operator in VBA). As of Access 2000, the period replaces the bang operator, which Access 2003 continues to support for backward compatibility. The period operator performs the following operations:
Other OperatorsThe remaining Jet operators are related to the comparison operators. These operators return True or False, depending on whether the value in a field meets the chosen operator's specification when used in a WHERE clause criterion. A True value causes a record to be included in a query; a False value rejects the record. When you use these operators in validation rules, entries are accepted or rejected based on the logical value returned by the expression. Table 10.4 lists the four other operators used in Access queries and validation rules.
You use the wildcard characters * and ? with the Like operator the same way that you use them in DOS. The * (often called star or splat) takes the place of any number of characters. The ? takes the place of a single character. For example, Like "Jon*" returns True for values such as Jones or Jonathan. Like "*on*" returns True for any value that contains on. Like "FILE????" returns True for FILENAME, but not for FILE000 or FILENUMBER. Wildcard characters can precede the characters that you want to match, as in Like "*son" or Like "????NAME".
Except for Is, the operators in this other category are equivalent to the SQL reserved words LIKE, IN, and BETWEEN. Jet includes these operators to promote compatibility with SQL. You can create each of these operators by combining other VBA operators or functions. Like "Jon*" is the equivalent of VBA's InStr(Left(FieldName, 3), "Jon"); In("CA", "OR", "WA") is similar to InStr("CAORWA", FieldName), except that matches would occur for the ambiguous AO and RW. Between 1 And 5 is the equivalent of >= 1 And <= 5. Tip Always use Between...And, not the >= and <= comparison operators, to specify a range of dates. You must repeat the field name when using the comparison operators, as in DateValue >= #1/1/1999# And DateValue <= #12/31/1999#. Between syntax is shorter and easier to understand, as demonstrated by DateValue Between #1/1/1999# And #12/31/1999#. |