tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Other Sites

CheckYourMath BigActivities DigMinecraft
Share this page:

MS Access: Case Statement

Learn 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 Microsoft Access Case statement 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 is a 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 to condition_n are 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 to result_n is the code that is executed once a condition is found to be true.

Note

If no condition is met, then the Else portion of the Case statement will be executed. It is important to note that the Else portion is optional.

Applies To

The Case function can be used in the following versions of Microsoft Access:

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