Between...And Operator (SQL Only)
See Also
Description
Determines whether the value of an expression lies within a specified range of values. This operator can only be used within SQL statements .
Syntax
expr [Not] Between value1 And value2
Remarks
The Between...And operator uses the following arguments.
Argument Description
expr Expression identifying the field that contains the data you want to evaluate
value1, value2 Expressions against which you want to evaluate expr
If the value of expr is between value1 and value2 (inclusive), the Between...And operator returns True; otherwise, it returns False. You can include the Not logical operator to evaluate the opposite condition (that is, whether expr lies outside the range defined by value1 and value2).
You might use Between...And to determine whether the value of a field falls within a specified numeric range. The following example determines whether an order was shipped to a location within a range of ZIP codes. If the ZIP Code is between 98101 and 98199, the IIf function returns "Local". Otherwise, it returns "Nonlocal".
SELECT IIf([ZIP] Between 98101 And 98199, "Local", "Nonlocal")
FROM Publishers
If expr, value1, or value2 is Null