BETWEEN( ) function

Returns a logical value indicating whether the specified value falls within a range.

Syntax

BETWEEN(value, min, max)

Parameters

value

Numeric, Character, or Datetime. The field, expression, or literal value to test.

min

Numeric, Character, or Datetime. The minimum value of the range.

max

Numeric, Character, or Datetime. The maximum value of the range.

Output

Logical. Returns T (true) if value is greater than or equal to the min value, and less than or equal to the max value. Returns F (false) otherwise.

Remarks

The value, min, and max parameters can be numeric, character, or datetime fields, expressions, or literal values, but all three parameters must belong to the same data category. The range evaluating to T (true) includes the min and max values. The order of the min and max parameters does not matter because ACL automatically identifies which value is the minimum and which value is the maximum.

Numeric parameters

When numeric fields have different decimal precision, the comparison uses the highest level of precision found in any of the fields.

Character parameters

The BETWEEN( ) function is case-sensitive when used with character data. When it compares characters, “a” is not equivalent to “A”. If you are working with data that includes inconsistent case, you can use the UPPER( ) or LOWER( ) functions to convert the values to consistent case before using BETWEEN( ).

The return value of BETWEEN( ) can be affected by the SET EXACT option (Exact Character Comparisons option in the Options dialog box). This option is turned off by default, and when it is off ACL compares two character strings using only the characters in the shorter string. If the SET EXACT option is turned on, ACL compares two character strings using all the characters in the longer string. A different result for identical character parameters is possible, as illustrated below. The square brackets [ ] identify the characters being compared.

value parameter

min parameter

max parameter

BETWEEN( ) result is T (True). SET EXACT OFF/Exact Character Comparisons = unchecked.

first comparison

[Dep]artments

[Dep]

“Dep” is greater than or equal to “Dep”

second comparison

[Department]s

[Department]

“Department” is less than or equal to “Department”

BETWEEN( ) result is F (False). SET EXACT ON/Exact Character Comparisons = checked.

first comparison

[Departments]

[Dep]

“Departments” is greater than or equal to “Dep”

second comparison

[Departments]

[Department]

“Departments” is not less than or equal to “Department”

If you want to ensure that the Exact Character Comparisons option is not used with the BETWEEN( ) function, check that the option is deselected in the Table tab in the Options dialog box ( Tools > Options). If you are using a script, you can add the SET EXACT OFF command before the BETWEEN( ) function appears. If required, you can restore the previous state with the SET EXACT ON command.

Datetime parameters

You are not prevented from mixing date, datetime, and time values in the BETWEEN( ) function’s three parameters, but mixing these Datetime subtypes can give results that are not meaningful. ACL uses serial number equivalents to process datetime calculations, so even if you are interested in only the date portion of a datetime value, the time portion still forms part of the calculation. For example:

BETWEEN(`20141231`,`20141229`,`20141231`) = True

BETWEEN(`20141231 120000`,`20141229`,`20141231`) = False

If we look at the serial number equivalent of these two expressions, we can see why the second one evaluates to false:

BETWEEN(42003.000000, 42001.000000, 42003.000000) = True

BETWEEN(42003.500000, 42001.000000, 42003.000000) = False

The serial number 42003.500000 is greater than 42003.000000 and therefore is out of range, even though the two dates are identical.

To avoid this problem, you can use other functions to harmonize Datetime subtypes. For example, this expression, which uses the same initial values as the second example above, evaluates to T (true):

BETWEEN(CTOD(DATE(`20141231 120000`,"YYYYMMDD"),"YYYYMMDD"),`20141229`,
`20141231`)

A date, datetime, or time field specified for any of the parameters can use any date, datetime, or time format, as long as the field definition correctly defines the format.

When specifying a literal date, datetime, or time value for any of the parameters, you are restricted to the formats in the table below, and you must enclose the value in backquotes – for example, `20141231`. For datetime values, you can use any combination of date, separator, and time formats. The date must precede the time, and you must use a separator between the two. Times must be specified using the 24-hour clock. Offsets from Coordinated Universal Time (UTC) must be prefaced by a plus sign (+) or a minus sign (-). Do not use any separators such as slashes (/) or colons (:) between the individual components of dates or times.

Date formats

Separator formats

Time formats

Examples

YYYYMMDD

single blank space

hhmmss

`20141231`

YYMMDD

the letter ‘t’

hhmm

`20141231 235959`

the letter ‘T’

hh

`141231 2359`

 

+/-hhmm

(UTC offset)

`141231t23`

+/-hh

(UTC offset)

`141231t235959-0500`

(Note: Do not use hh alone in the main time format with data that has a UTC offset. For example, avoid: hh+hhmm. Results can be unreliable.)

`141231T2359+01`

Examples

Example Return value

BETWEEN(500,400,700)

T

BETWEEN(100,400,700)

F

BETWEEN("B","A","C")

T

BETWEEN("b","A","C")

F

The function is case-sensitive, and lowercase “b” does not fall between uppercase “A” and “C”.

BETWEEN(`141230`,`141229`,`141231`)

T

BETWEEN(Login_time,`t070000`,`t090000`)

Returns ‘T’ for all values in the Login_time field from 07:00:00 AM to 09:00:00 AM, and ‘F’ otherwise.

Create a filter to view a salary range

The following example opens the Employee_List sample table and applies a filter that limits the records displayed to include only employees that earn a salary greater than or equal to $40,000.00, and less than or equal to $50,000.00.

OPEN Employee_List
SET FILTER TO BETWEEN(Salary, 40000.00, 50000.00)


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback