BETWEEN

BETWEEN

Top  Previous  Next

 

The BETWEEN selection clause operator compares a field or evaluated expression against two other fields, evaluated expressions or literal values, testing whether the value of the first item lies between the other two values.

 

 

Format

 

field BETWEEN {NO.CASE} value1 value2

 

 

where

 

fieldis the first field or evaluated expression to be compared.

 

value1is the low end of the range of values to be selected.

 

value2is the high end of the range of values to be selected.

 

 

The BETWEEN selection clause operator returns True if field is greater than or equal to value1 and less than or equal to value2. The optional NO.CASE qualifier causes a case insensitive comparison to be applied.

 

 

When applied to multivalued fields, the test is applied to each value in turn. Note that the query

LIST ORDERS WITH PART.NO BETWEEN 200 299

is not the same as

LIST ORDERS WITH PART.NO >= 200 AND PART.NO <= 299

if PART.NO is a multivalued field. The first query selects only those records which include part numbers in the range 200 to 299. The second query selects those records which include a part number that is greater than or equal to 200 and another part number that is less than or equal to 299.