The tutorial today will discuss about Ms Access Case Statement. Case Statement is used to specify and give commands to some conditions, similar to if-then-else statement. There are two Case Statement: one for compare an expression to a set of simple expressions to determine the result, while the other one is to evaluate a set of Boolean expressions to determine the result.
The Case Statement only can be used in VBA Code. Syntax for Ms Access Case Statement:
Select Case arguments
Case condition
result
Case Else
result_else
End Select
Parameters:
Argument: the string or numeric value. It is the value that you are comparing to the list of conditions.
Condition: 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. You can use multiple Case Condition in one code.
Result: Code that is executed once a condition is found to be true. Following the Condition, it can has more than one result in a code.
Else: the other arguments for condition and result that not listed above.
Example:
Table Name: Student Grade
| Name | Grade |
| Adrianne | 100 |
| Betsy | 64 |
| Clara | 85 |
| Doug | – |
| Evan | 76 |
Select Case Grade
Case 100
[GradeName] = “Perfect”
Case 85 to 99
[GradeName] = “Very Good”
Case 65 to 84
[GradeName] = “Good”
Case Is < 65
[GradeName] = “Failed”
Case Else
[GradeName] = “Disqualified”
End Select
The return value will depends on the Grade entry. So the result will be:
| Name | Grade | GradeName |
| Adrianne | 100 | Perfect |
| Betsy | 64 | Failed |
| Clara | 85 | Very Good |
| Doug | – | Disqualified |
| Evan | 76 | Good |
For Query and SQL query, use SWITCH function instead of CASE. This is because CASE only supported for VBA and other programming language. You can see the example of SWITCH function here:
The picture above is the database of books. The Region Field will determine the location of the shelves of each book.
Shelf:
Switch(
[Region] = “A”, “Shelf Number 200”,
[Region]= “B”, “Shelf Number 400”,
[Region] = “C”, “Shelf Number 700”
)
The result in the query can be seen like this:
Related Access Database
- ms access case statement
- access case statement