MS Access: Case Statement
This MSAccess tutorial explains how to use the Access Case statement with syntax and examples.
Description
The Microsoft Access Case statement can only be used in VBA code. It has the functionality of an IF-THEN-ELSE statement.
Syntax
The syntax for the Case statement in MS Access is:
Select Case test_expression Case condition_1 result_1 Case condition_2 result_2 ... Case condition_n result_n [ Case Else result_else ] End Select
Parameters or Arguments
- test_expression
- String or numeric value. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)
- condition_1, condition2, ... condition_n
- Evaluated in the order listed. Once a condition is found to be true, the Case statement will execute the corresponding code and not evaluate the conditions any further.
- result_1, result_2, ... result_n
- Code that is executed once a condition is found to be true.
Returns
The Case statement executes the corresponding code for the first condition that is found to be TRUE.
If no condition is met, then the Else clause in the Case statement will be executed. The Else clause is optional.
If the Else clause is omitted and no condition is found to be true, then the Case statement will do nothing.
Applies To
The Case function can be used in the following versions of Microsoft Access:
- Access 2019, Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000
Example in VBA Code
The Case statement can only be used in VBA code in Microsoft Access.
Let's look at a simple example:
Select Case [Region] Case "N" [RegionName] = "North" Case "S" [RegionName] = "South" Case "E" [RegionName] = "East" Case "W" [RegionName] = "West" End Select
You can also use the To keyword to specify a range of values.
For example:
Select Case LNumber Case 1 To 10 [RegionName] = "North" Case 11 To 20 [RegionName] = "South" Case 21 To 30 [RegionName] = "East" Case Else [RegionName] = "West" End Select
You can also comma delimit values.
For example:
Select Case LNumber Case 1, 2 [RegionName] = "North" Case 3, 4, 5 [RegionName] = "South" Case 6 [RegionName] = "East" Case 7, 11 [RegionName] = "West" End Select
And finally, you can also use the Is keyword to compare values.
For example:
Select Case LNumber Case Is < 100 [RegionName] = "North" Case Is < 200 [RegionName] = "South" Case Is < 300 [RegionName] = "East" Case Else [RegionName] = "West" End Select
Advertisements