MS Access If-Then-Else statement runs a specific statement or a block of statements depending on the value of a given condition. If–Then–Else Statements can be nested to as many levels as you need.
In Microsoft Access, the If-Then-Else statement allows you to evaluate a condition and then choose which code block to execute. It has the following syntax:
If condition Then
[Execute if condition is True]
Else
[Execute if condition is False]
End If
For example, if you wanted to check if a value is greater than 1000 and display “Large” if it is, you can use the following code:
If [Amount] > 1000 Then
Response = "Large"
Else
Response = [Amount]
End If
Tip: To run only one statement when a condition is true, use the single-line syntax of the If-Then-Else statement. The following example shows a single-line VBA, omitting the Else keyword to quit the program.
If MsgBox("Quit Program?", vbYesNo, "Quit") = vbYes Then DoCmd.Quit
To define two blocks of executable statements: One block runs if the condition is True, and the other block runs if the condition is False.
If intVal = 0 Then
MsgBox"Value= " & 0
Else
MsgBox"Value is more than Zero"
End If
You can also add ElseIf statements to If-Then-Else statement to run a second condition or more if the first condition is False. For example:
If intVal = 0 Then
MsgBox "Value= " & 0
ElseIf intVAl=1 Then
MsgBox "Value= " & 1
Else
MsgBox "Value is more than one"
End If
MS Access If-Then-Else statement has the same functionality of Select Case Statement. It returns the corresponding code for the first condition if found to be true. If no condition is met, then the ElseIf or Else statement will be executed.
Tip: “For readability, you may want to use a Select Case statement rather than multiple levels of nested If-Then-Else statement”.
Download an Example
The downloadable example uses the “If-Then-Else statement” to evaluate the length of a given number and then translate it into words: e.g. “ones, tens, hundreds, thousands, millions, etc.”.