ActiveDoc

Between()

This function determines whether the value of one expression is between the values of two others. Both boundaries are included in the range—it's an "inclusive" test. BETWEEN() works on almost every field type in Visual FoxPro; only General is excluded.

Usage

lResult = BETWEEN( uExpression, eLower, eUpper )

Parameter	Value	Meaning
uExpression	Character, Currency, Date, DateTime, Double, Integer, Logical, Memo, Numeric	The value to be compared to the specified boundaries.
uLower	Same or similar type as uExpression	The lower boundary.
uUpper	Same or similar type as uExpression	The upper boundary.
lResult	.T.	uExpression is between uLower and uUpper.
	.F.	uExpression is not between uLower and uUpper.

BETWEEN() does some conversion between related data types. If any of the three expressions is DateTime, dates are converted to DateTime by adding a time of "12:00:00 AM" (midnight). Watch out if you do this—if the lower boundary is DateTime (and doesn't have a time of midnight) and uExpression is Date and has the same date as the lower boundary, BETWEEN() returns .F., because uExpression gets the default time of midnight.

Numeric, currency, integer and double values can be mixed with BETWEEN() without problem. So can character data and memo fields holding characters.

BETWEEN() is Rushmore-optimizable. Our tests show that, with variables, BETWEEN() is between 25% and 40% faster than using the comparison operators. That is:

?BETWEEN(x,y,z)

is 25–40% faster than:

?x>=y AND x<=z

For Rushmore-optimizable record operations, the improvement isn't as great—our tests show BETWEEN() can be as much 6% to 7% faster than performing two comparisons, but not in every case. In some of our tests, the two forms were about even. In queries, we didn't see any significant differences between the SQL BETWEEN clause and the BETWEEN() function. Our inclination is to use the SQL BETWEEN there because it's part of the SQL standard.

If eExpression is null, BETWEEN() returns .NULL. If either boundary is null, BETWEEN() uses some smarts about the notion of lower and upper boundaries. If the lower boundary is null, but eExpression is greater than the upper boundary, the function returns false. Similarly, if eUpper is null, but eExpression is less than the lower boundary, BETWEEN() returns .F. because the result must be false. When BETWEEN() can't tell because of a null boundary, it returns .NULL.

Example

? BETWEEN(27, 12, 50)   && returns .T. ? BETWEEN(27, 27, 30)   && returns .T. ? BETWEEN("foxpro", "flocks", "sheep")  && returns .T. ? BETWEEN(DATE(), DATETIME(), DATE()+1) && returns .F.

See Also

InList(), Max(), Min(), Select-SQL

View Updates

Copyright © 2002 by Tamar E. Granor, Ted Roche, Doug Hennig, and Della Martin. All Rights Reserved.