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 IsNull Function with Examples in SQL Query and VBA Code

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

  • case statement in access
  • case statement in access database
  • case statement access
  • ms access case statement
  • case condition used in joins in ms access
  • swtich case in ms access
  • case statement in access db
  • case statement in ms access
  • case when access
  • case when statement in access
Read Also:  Nonprofit Fundraising and Donation Management Software for Ms Access


Access Database Tags: #access case statement between #access case statement in where clause #access case statement multiple conditions #access database case statement syntax #access vba case statement between #access vba case statement multiple conditions #case statement in an access query #case statement in ms access database #ms access and case statement #ms access case statement #ms access case statement between #ms access case statement in where clause

MS Access VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access
MS Access DB and MDB Viewer Software
MS Access DB and MDB Viewer Software
Access DB Viewer is the software to
MS Access Validation Rule Lookup Table Examples
MS Access Validation Rule Lookup Table Examples
The Validation Rule in Microsoft Access is


Must read×

Top