Between...And Operator (SQL Only)

See AlsovboprBetweenAndSee


Determines whether the value of an expression lies within a specified range of values.  This operator can only be used within SQL statements29F05E5.


expr [Not] Between value1 And value2


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 Null1DDW7C0, Between...And returns a Null.