Tue. Oct 15th, 2024
MS Access Case Statement Syntax in Multiple Conditions
MS Access Case Statement Syntax in Multiple Conditions

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

Read Also:  MS Access Validation Rule Lookup Table Examples

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:

Ms Access Case Statement-1
Ms Access Case Statement-1

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:

Ms Access Case Statement-2
Ms Access Case Statement-2

Related Access Database

  • ms access case statement
  • access case statement