Part I: Access Building Blocks
184
Miscellaneous operators
Access has three very useful miscellaneous operators:
Between...And Range
In List comparison
Is Reserved word
The Between...And operator
Between...And determines whether an expression’s value falls within a range of values:
expression Between value1 And value2
If the value of the expression falls within value1 and value2, or is the same as value1 or value2, the
result is True; otherwise, it’s False.
The following examples show how to use the Between...And operator:
[TotalCost] Between 10000 Returns True if the TotalCost is between
And 19999 10,000 and 19,999, or equal to 10,000 or 19,999.
[SaleDate] Between Returns True when the SaleDate occurs
#1/1/2012# And #12/31/2012# within the year 2012.
The Between...And operator can also be used with Not operator to negate the logic:
Not [SaleDate] Between Returns True only when SaleDate is not within
#1/1/2012# And #3/31/2012# the first quarter of 2012.
The In operator
The In operator determines whether an expression’s value is the same as any value within a list.
The general syntax of In is:
Expression In (value1, value2, value3, ...)
If the expression’s value is found within the list, the result is True; otherwise, the result is False.
The following example uses the In operator as a query’s criteria in the Category column:
In (‘SUV’,’Trucks’)
This query displays only those models that are SUVs or trucks.
The In operator is also used in VBA code:
If [tblCustomers].[City] In(“Seattle”, “Tacoma”) Then
In this case the body of the If...Then...Else statement executes only if the City field is either Seattle
or Tacoma.
