Microsoft Access is a very versatile data-handling tool. There are numerous control statements that turn the program flow based on the value of certain expressions. Such statements are the Select Case statement, If...Then...Else statements, the For Loop etc.
The Select Case statement is the multiway branching statement. If the probable actions based on the value of a variable are numerous, this statement is used. It is an easy way to send execution to different parts of the code depending on an expression.
Select Case (expression)
Case option1
statement1
Case option2
statement2
.
.
.
Case optionn
statementn
Case Else
statementd
End Select
| <expression> | The further actions depends on the evaluation of this argument. This is a required argument and can be a numeric or string expression | ||||||||
| <optionx> | This contains the possible values of the <expression>
argument. It can be more than one value, a range of values or
a combination of values and comparison operators. It can be one
or a combination of the following variations.
The <comparison operator> indicate the relation between two expressions. They can be greater than (>), less than (<), less than equal to (<=), greater than equal to (>=), not equal to (<>) or equal to (=). Duplicate values are not allowed. It must be of the same type as the <expression>. |
||||||||
| <statementx> | These are the statements executed when the case constant matches the expression value. |
The value of <expression> is compared with each of the Case option values. When a match is found, the code sequence following the matching case literal is executed. If none of the provided case constants matches the value of the expression, the Case Else statements are executed. After the statements are executed, the control passes to the statement just after the End Select. The Case Else is optional.
Multiple expressions or ranges can be used in each Case clause. For example,
Case 2 To 6, 9 To 19, 111, 113
You can use character strings and variables too. In this example the statements for this Case option will be executed if the string matches “great,” strings that fall between "nail" To "pair" in alphabetic order and string equivalent to the value of the variable NVar:
Case "great", "nail" To "pair", Nvar.
Example:
Select Case Nvar
Case IS < 5
Debug.Print "Less than 5"
Case 6, 7, 8
Debug.Print "Between 6 and 8"
Case 8 TO 12
Debug.Print "Greater than 8"
Case Else
Debug.Print "Not between 1 and 12"
End Select
You can have nested Select Case statements too. There should be an End Select statement for each nested Select Case.
The If...Then...Else statements evaluates a different expression for each ElseIf statement, the Select Case statement evaluates an expression only once, at the beginning of the control structure.
Select Case statement is more efficient than the nested ifs. A compiler will run much faster for a Select Case statement.
Select Case can be more helpful when evaluating a single expression that has several possible actions. The TypeOf objectname Is objecttype clause cannot be used with the Select Case statement.
If your expression involves complex calculations or reads from an object, then Select Case statement is more efficient. The reason is that in IF statement you would need to evaluate the expression each time, whereas with Select Case statement it is evaluated only once. In that case If ..Then..Else statement would slow down your process.
The Select Case statement test only one expression whereas the If ..Then..Else statement can evaluate different expressions too.
Not only is the Select Case faster than the If ..Then..Else, but also they are easier to read through and debug.