You can use the Select Case statement in Microsoft Access to evaluate a given expression and then execute a set of instructions based on the result of the evaluation. It is similar to the If-Then-Else statement, except that it allows for multiple conditions to be tested without having to nest them. The expression to be evaluated is provided as an argument, and then one or more Case clauses can be added to check for specific values or ranges of values. If a Case clause matches the expression, the corresponding code will be executed. If none of the Case clauses match, then the Else clause will be executed. For example, the following code checks if a variable called “age” is between 10 and 20, and then prints out the appropriate response:
Select Case age
Case 10 To 20
MsgBox "You are between 10 and 20 years old!"
Case Else
MsgBox "You are not between 10 and 20 years old!"
End Select
VBA Code of Select Case Statement:
Dim intDig as string
Select Case intDig
Case Is = 1
strVal = "Ones"
Case Is = 2
strVal = "Tens"
Case Is = 3
strVal = "Hundreds"
Case Is = 4, 5, 6
strVal = "Thousands"
Case Else
strVal = "More than Thousands "
End Select
MS Access Select Case Statement has the same functionality as the IF-THEN-ELSE statement. However, the Select Case statement in MS Access returns the corresponding code for the first condition if it is found to be true. If it is not, then the program will execute the Else clause statement. The Else clause, however, is optional as it will do nothing if else clause is skipped.
Download Example:
The downloadable sample uses the Select Case statement to evaluate the length of a given number and then translate that into words: e.g., “ones, tens, hundreds, thousands, millions, etc.”.