>

Between...And Operator

Description

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

Syntax

expr [Not] Between value1 And value2

The Between...And operator syntax has these parts.

Part

Description

expr

Expression identifying the field that contains the data you want to evaluate.

value1, value2

Expressions against which you want to evaluate expr.


Remarks

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

Because wildcards, such as *, are treated as literals, you cannot use them with the Between...And operator. For example, you cannot use 980* and 989* to find all postal codes that start with 980 to 989. Instead, you have two alternatives for accomplishing this. You can add an expression to the query that takes the left three characters of the text field and use Between...And on those characters. Or you can pad the high and low values with extra characters — in this case, 98000 to 98999, or 98000 to 98999 – 9999 if using extended ZIP codes. (You must omit the – 0000 off the low values because otherwise 98000 is dropped if some ZIP codes have extended sections and others do not.)

See Also

IN Clause, SQL Expressions.

Specifics (Microsoft Access)

In Microsoft Access, you can use the Between...And operator in a query expression or in a calculated control on a form or report.

You can use the Between...And operator in the Criteria field in the query design grid to create a parameter query. The Between...And operator enables you to prompt the user to enter a range of values by which the query is restricted. The expression supplied for the value1 argument serves as the prompt for the beginning of the range, and the expression supplied for the value2 argument serves as the prompt for the end of the range.

For example, suppose you have an Orders table that has an OrderDate field. Create a new query in the query design grid and drag the OrderDate field to the first Field cell in the grid. In the Criteria field, enter the following statement.


Between [Enter beginning date:] And [Enter ending date:]
When you run the query, you will first be prompted with a dialog box that says, "Enter the beginning date for the range." Once you enter a value in that dialog, you will be prompted by a second dialog box that says, "Enter the ending date for the range." Assuming you enter valid dates, your results will include all the values in the OrderDate field that either match or fall between the dates you specified as parameters.

You can also use the Between...And operator in a calculated control to determine whether the value of the control 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 field is between 98101 and 98199, the IIf function returns "Local." Otherwise, it returns "Nonlocal."


= IIf([ZIP] Between "98101" And "98199", "Local", "Nonlocal")
If any of the arguments supplied for the Between...And expression is Null, Between...And returns a Null.