Table of operators

An operator is a sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators. Access supports a variety of operators, including arithmetic operators such as +, -, multiply (*), and divide (/), in addition to comparison operators for comparing values, text operators for concatenating text, and logical operators for determining true or false values. This article provides details about using these operators.

Note: Beginning in Access 2010, the Expression Builder has IntelliSense, so you can see what arguments your expression requires.

In this article

Arithmetic operators

Comparison operators

Logical operators

Concatenation operators

Special operators

Arithmetic operators

You use the arithmetic operators to calculate a value from two or more numbers or to change the sign of a number from positive to negative or vice versa. Operator Purpose Example + Sum two numbers. [Subtotal]+[SalesTax] - Find the difference between two numbers or indicate the negative value of a number. [Price]-[Discount] * Multiply two numbers. [Quantity]*[Price] / Divide the first number by the second number. [Total]/[ItemCount] \ Round both numbers to integers, divide the first number by the second number, and then truncate the result to an integer. [Registered]\[Rooms] Mod Divide the first number by the second number, and then return only the remainder. [Registered] Mod [Rooms] ^ Raise a number to the power of an exponent. Number ^ Exponent

Top of Page

Comparison operators

You use the comparison operators to compare values and return a result that is True, False, or Null.

Operator	Purpose	Example
<	Returns True if the first value is less than the second value.	Value1 < Value2
<=	Returns True if the first value is less than or equal to the second value.	Value1 <= Value2
>	Returns True if the first value is greater than the second value.	Value1 > Value2
>=	Returns True if the first value is greater than or equal to the second value.	Value1 >= Value2
=	Returns True if the first value is equal to the second value.	Value1 = Value2
<>	Returns True if the first value is not equal to the second value.	Value1 <> Value2

Note: In all cases, if either the first value or the second value is null, the result is then also null. Because null represents an unknown value, the result of any comparison with a null value is also unknown.

Top of Page

Logical operators

You use the logical operators to combine two Boolean values and return a true, false, or null result. Logical operators are also referred to as Boolean operators. Operator Purpose Example And Returns True when Expr1 and Expr2 are true. Expr1 And Expr2 Or Returns True when either Expr1 or Expr2 is true. Expr1 Or Expr2 Eqv Returns True when both Expr1 and Expr2 are true, or when both Expr1 and Expr2 are false. Expr1 Eqv Expr2 Not Returns True when Expr is not true. Not Expr Xor Returns True when either Expr1 is true or Expr2 is true, but not both. Expr1 Xor Expr2

Top of Page

Concatenation operators

You use the concatenation operators to combine two text values into one. Operator Purpose Example & Combines two strings to form one string. string1 & string2 + Combines two strings to form one string and propagates null values (if one value is Null, the entire expression evaluates to Null). string1 + string2

Top of Page

Special operators

You use the special operators to return a True or False result as described in the following table. Operator Purpose Example Is Null or Is Not Null Determines whether a value is Null or Not Null. Field1 Is Not Null Like "pattern" Matches string values by using the wildcard operators ? and *. Field1 Like "instruct*" Between val1 And val2 Determines whether a numeric or date value is found within a range. Field1 Between 1 And 10 - OR - Field1 Between #07-01-07# And #12-31-07# In(val1,val2...) Determines whether a value is found within a set of values. Field1 In ("red","green","blue") - OR - Field1 In (1,5,7,9)