Conditional Statements

The If...Then Statement

Introduction

The comparison operators we have reviewed above are used to know the state of two values but they don't provide a mechanism to exploit there result. After getting the result of a comparison, to use it effectively, you can formulate a condition statement. Microsoft Access and Microsoft Visual Basic support this through various keywords and functions.

The If...Then statement examines the truthfulness of an expression. Structurally, its formula is:

If Condition Then Statement

The program will examine the Condition. This condition can be a simple expression or a combination of expressions. If the Condition is true, then the program will execute the Statement. This can be illustrated as follows:

There are two ways you can use the If...Then statement. If the conditional expression is short enough, you can write it on one line using the following formula:

If Condition Then Statement

In the following example, if the text box named txtGender of a form displays Male, the background color of the Detail section would be colored in light blue:

Private Sub Form_Current()
    If txtGender = "Male" Then Detail.BackColor = 16772055
End Sub

If there are many statements to execute as a truthful result of the condition, you should write the statements on subsequent lines. Of course, you can use this technique even if the condition you are examining is short. If then you use the Statement on a different line, you must terminate the conditional statement with the End If expression. The formual used is:

If Condition Then
    Statement
End If

The example above can be re-written as follows:

Private Sub Form_Current()
    If txtGender = "Male" Then
        Detail.BackColor = 16772055
    End If
End Sub

If the condition needs to cover many lines of code, the syntax to apply is:

If Condition Then
    Statement1
    Statement2
    Statement_n
End If

Here is an example:

Private Sub Form_Current()
    If Gender = "Male" Then
        Detail.BackColor = 16772055
        FormHeader.BackColor = 16752478
        FormFooter.BackColor = 14511872
    End If
End Sub

Microsoft Access doesn't use conditionals statements like traditional computer languages do. It relies on special condition-oriented functions to perform the same operations. One of these functions is called Nz.

The Nz() function is used to check the value of an expression or a control. Its syntax is:

Nz(Value, [ValueIfNull])

The function checks the value of the (first) argument. If the Value is null, the function returns 0 or an empty string. The second argument is optional. You can provide it as an alternative to 0 in case the Value argument is null. This means, that, when the first argument is null, instead of returning 0 or an empty string, the Nz() function would return to the value of the second argument.

1. Start Microsoft Access and open the Exercise3 database
2. Open the Employment Application form in Design View and click the top text box
3. In the Properties window, click the Event button and double-click On Lost Focus
4. Click its ellipsis button to open Microsoft Visual Basic and implement its Change event as follows:
    

   Private Sub txtFirstName_LostFocus() Dim FirstName As String Dim LastName As String Dim FullName As String FirstName = Nz([txtFirstName]) LastName = Nz([txtLastName]) FullName = LastName & ", " & FirstName [txtFullName] = FullName If LastName = "" Then [txtFullName] = FirstName End Sub

5. In the Object combo box, select txtLastName
6. In the Procedure combo box, select Change and implement its event as follows:
    

   Private Sub txtLastName_LostFocus() Dim FirstName As String Dim LastName As String Dim FullName As String FirstName = Nz([txtFirstName]) LastName = Nz([txtLastName]) FullName = LastName & ", " & FirstName [txtFullName] = FullName If LastName = "" Then [txtFullName] = FirstName End Sub

7. Return to Microsoft Access and switch the form to Form View
8. Click the top text box, type Julienne and press Tab. Notice that only the first name displays in the Full Name text box
    
   
9. In the other text box, type Palace and Press Enter
    
   
10. Close the form
11. When asked whether you want to save it, click Yes

IsEmpty(Expression)

IsNull(Expression)

1. On the Database Window, if necessary, click Forms, right-click Assignment and click Design View
2. Access the form's code and change the Click event of the top button as follows:
    

   Private Sub cmdCreateAccount_Click() Dim strFirstName As String Dim strMiddleInitial As String Dim strLastName As String Dim strFullName As String Dim strUsername As String strFirstName = Nz([txtFirstName]) If Not IsNull(txtMI) Then strMiddleInitial = txtMI & ". " strLastName = Nz([txtLastName]) ' Create a username made of the last name followed by the middle initial strUsername = txtLastName & strMiddleInitial ' Create a full name as the first name followed by the last name strFullName = txtFirstName & " " & strMiddleInitial & txtLastName txtFullName = strFullName txtUsername = strUsername End Sub

3. Close the form
4. When asked whether you want to save it, click Yes

IsNumeric(Expression)

1. In the Database window, double-click the frmProcedures form
2. On the form, click the top Calculate button and notice that you receive a nasty error
    
   
3. Click Debug to open Microsoft Visual Basic and the Code Editor
4. On the Standard toolbar of Microsoft Visual Basic, click the Stop button
5. Change the procedures of the module of the form as follows:
    

   Option Compare Database Option Explicit

   Sub SquareSolution() ' Declare the necessary variables for the square Dim dblSide As Double Dim dblPerimeter, dblArea As Double ' Retrieve the value of the side If IsNumeric([txtSqSide]) Then dblSide = Nz([txtSqSide]) ' Calculate the perimeter and the are of the square dblPerimeter = dblSide * 4 dblArea = dblSide * dblSide ' Prepare to display the result in the appropriate text boxes txtSqPerimeter = dblPerimeter txtSqArea = dblArea End Sub

   Private Sub SolveRectangle() ' Declare the necessary variables for the rectangle Dim dblLength, dblHeight As Double Dim dblPerimeter, dblArea As Double ' Retrieve the values of the length and height If IsNumeric([txtRLength]) Then dblLength = Nz([txtRLength]) If IsNumeric([txtRHeight]) Then dblHeight = Nz([txtRHeight]) ' Calculate the perimeter and the area of the rectangle dblPerimeter = (dblLength + dblHeight) * 2 dblArea = dblLength * dblHeight ' Prepare to display the result in the appropriate text boxes txtRPerimeter = dblPerimeter txtRArea = dblArea End Sub

   Private Sub cmdBoxCalculate_Click() Dim dLen As Double Dim dHgt As Double Dim dWdt As Double Dim Area, Vol As Double If IsNumeric([txtBoxLength]) Then dLen = Nz([txtBoxLength]) If IsNumeric([txtBoxHeight]) Then dHgt = Nz([txtBoxHeight]) If IsNumeric([txtBoxWidth]) Then dWdt = Nz([txtBoxWidth]) Area = BoxArea(dLen, dHgt, dWdt) Vol = BoxVolume(dLen, dHgt, dWdt) txtBoxArea = Area txtBoxVolume = Vol End Sub

   Private Sub cmdCCalculate_Click() txtCircleCircumference = CircleCircumference() txtCircleArea = CircleArea() End Sub

   Private Sub cmdCubeCalculate_Click() Dim dblSide As Double Dim dblArea As Double Dim dblVolume As Double If IsNumeric([txtCubeSide]) Then dblSide = Nz([txtCubeSide]) dblArea = CubeArea(dblSide) dblVolume = CubeVolume(dblSide) txtCubeArea = dblArea txtCubeVolume = dblVolume End Sub

   Private Sub cmdECalculate_Click() Dim Radius1 As Double Dim Radius2 As Double If IsNumeric([txtEllipseRadius1]) Then Radius1 = Nz([txtEllipseRadius1]) If IsNumeric([txtEllipseRadius2]) Then Radius2 = Nz([txtEllipseRadius2]) SolveEllipse Radius1, Radius2 End Sub

   Private Sub cmdRCalculate_Click() SolveRectangle End Sub

   Private Sub cmdSqCalculate_Click() SquareSolution End Sub

   Function CircleCircumference() As Double Dim dblRadius As Double If IsNumeric([txtCircleRadius]) Then dblRadius = Nz([txtCircleRadius]) CircleCircumference = dblRadius * 2 * 3.14159 End Function

   Private Function CircleArea() As Double Dim dblRadius As Double If IsNumeric([txtCircleRadius]) Then dblRadius = Nz([txtCircleRadius]) CircleArea = dblRadius * dblRadius * 3.14159 End Function

   Sub SolveEllipse(SmallRadius As Double, LargeRadius As Double) Dim dblCircum As Double Dim dblArea As Double dblCircum = (SmallRadius + LargeRadius) * 2 dblArea = SmallRadius * LargeRadius * 3.14159 txtEllipseCircumference = dblCircum txtEllipseArea = dblArea End Sub

   Function CubeArea(Side As Double) As Double CubeArea = Side * Side * 6 End Function

   Function CubeVolume(Side As Double) As Double CubeVolume = Side * Side * Side End Function

   Function BoxArea(dblLength As Double, _ dblHeight As Double, _ dblWidth As Double) As Double Dim Area As Double Area = 2 * ((dblLength * dblHeight) + _ (dblHeight * dblWidth) + _ (dblLength * dblWidth) _ ) BoxArea = Area End Function

   Function BoxVolume(dblLength As Double, _ dblHeight As Double, _ dblWidth As Double) As Double Dim Volume As Double Volume = dblLength * dblHeight * dblHeight BoxVolume = Volume End Function

6. Return to Microsoft Access

The If...Then statement offers only one alternative: to act if the condition is true. Whenever you would like to apply an alternate expression in case the condition is false, use the If...Then...Else statement. The formula of this statement is:

If ConditionIsTrue Then
    Statement1
Else
    Statement2
End If

The condition, in this ConditionIsTrue, would be examined. If it produces a true result, then the first statement, in this case Statement1, would be executed. If the condition (ConditionIsTrue) is false, the second statement, in this case Statement2, would be executed.

Here is an example:

Private Sub Form_Current()
    If Gender = "Male" Then
        Detail.BackColor = 16772055
    Else
        Detail.BackColor = 13034239
    End If
End Sub

If any of the expressions needs more than one line of code to have a complete implementation, you can include it in the needed section before the end of the section. Here is an example:

Private Sub Form_Current()
    If Gender = "Male" Then
        Detail.BackColor = 16772055
        FormHeader.BackColor = 16752478
        FormFooter.BackColor = 14511872
    Else
        Detail.BackColor = 13034239
        FormHeader.BackColor = 7452927
        FormFooter.BackColor = 29670
    End If
End Sub

Because Microsoft Access doesn't support conditional statements such as the If...Then...Else combination, its relies on a function such as IIf() but, of course, this function can be used in any database or Microsoft Visual Basic expression. The syntax of the IIf() function is:

IIf(Condition, WhatToDoIfTrue, WhatToDoIfFalse)

When called, this function starts by checking the condition. Therefore, always make sure that you provide a condition that can produce a True or a False result. If the Condition evaluates to True, the expression in the second argument is applied. If the Condition produces False, the expression of the last argument applies.

The If...Then...ElseIf statement acts like the If...Then...Else, except that it offers as many choices as necessary. The formula is:

If Condition1 Then
    Statement1
ElseIf Condition2 Then
    Statement2
ElseIf Condition_n Then
    Statement_n
End If

The program will first examine the first condition, in this case Condition1. If Condition1 is true, then the program would execute the first statement, in this case Statment1, and stop examining conditions. But if Condition1 is false, then the program would examine Condition2 and act accordingly. Whenever a condition is false, the program would continue examining the conditions until it finds one that is true. Once a true condition has been found and its statement executed, the program would terminate the conditional examination at End If.

The above syntax pre-supposes that at lease one of the conditions would produce a true result. Sometimes, regardless of how many conditions you use, it is possible that none of them would produce a true result. Therefore, in anticipation of such occurrence, you should provide an alternate statement that would embrace any condition that doesn't fit in the possible true results. This is done by combining an If...Then...Else and an If...Then...ElseIf statements. The resulting syntax to use is:

If Condition1 Then
    Statement1
ElseIf Condition2 Then
    Statement2
ElseIf Condition3 Then
    Statement3
Else
    Statement_False
End If

In this case, if neither of the If and ElseIfs conditions was validated, then the last statement, in this case Statement_False, would execute.

Practical Learning: Using If...Then...ElseIf

The Select Case Statement

Introduction

If you have a large number of conditions to examine, the If...Then...Else will go through each one of them, which could take long (although usually transparent to the user). Microsoft Visual Basic offers an alternative of jumping to the statement that applies to the state of the condition. This is performed through the Select Case statement.

The syntax of the Select Case statement is:

Select Case Expression
    Case Expression1
        Statement1
    Case Expression2
        Statement2
    Case Expression_n
        Statement_n
End Select

The Expression is evaluated it once to get a general result. Then the result of of the Expression factor is compared with the ExpressionX of each case. Once it finds one that matches, it would execute the corresponding StatementX.

Here is an example:

Private Sub cboMembership_AfterUpdate()
    Dim strMembership As String
    
    strMembership = [cboMembership]
    
    Select Case strMembership
        Case "Teen"
            txtPrice = "$25"
        Case "Adult"
            txtPrice = "$50"
        Case "Senior"
            txtPrice = "$35"
    End Select
End Sub

If you anticipate that there could be no match between the Expression and one of the Expressionn, you can use a Case Else statement at the end of the list. The statement would then look like this:

Select Case Expression
    Case Expression1
        Statement1
    Case Expression2
        Statement2
    Case Expression3
        Statement3
    Case Else
        Statement_n
End Select

Select...Case-Related Functions: Choose()

Once again, since Microsoft Access doesn't inherently provide a programming environment, it relies on logical functions to take care of this aspect. The Choose() function is one of those that can test a condition and provide alternatives. The Choose() function works like nested conditions. It tests for a condition and provides different outcomes depending on the result of the test. Its syntax is:

Choose(Condition, Outcome1, Outcome2, Outcome_n)

The first argument of this function is the condition that should be tested. It should provide a natural number. After this test, the Condition may evaluate to 1, 2, 3, or more options. Each outcome is then dealt with. The first, Outcome1, would be used if the Condition produces 1. The second, Outcome2, would be used if Condition produces 2, etc.

Practical Learning: Using the Choose Function

Select...Case-Related Functions: Switch()

We have seen that the IIf() function is used to check a condition and can perform one of two statements depending on the result of the condition. In some expressions, there will be more than one condition to check. Although you can nest IIf() functions to create a complex expression, Microsoft Access provides another function that can perform this task. The function is called Switch and its syntax is:

Unlike IIf(), the Switch() function does not take a fixed number of arguments. It takes as many combinations of <Expression -- Statement>s as you need. Each expression is evaluated. If the expression evaluates to true, the statement that follows it executes. Although you can spend a great deal of time tuning a conditional expression such as one involving a Switch() function, it is still possible that none of the expressions evaluates to true. In this case, you can add a last expression as True and provide a subsequent statement to use. The syntax you would use is:

Practical Learning: Using the Switch Function

1. From the Forms section of the Database window, right-click the Operations form and click Design View:
    
   
2. On the Standard toolbar, click the Code button
3. In the Object combo box, select fraOperations
4. In the Procedure combo box, select Click and implement the event as follows:
    

   Private Sub fraOperations_Click() txtResult = Switch([fraOperations] = 1, Nz([txtNumber1]) + Nz([txtNumber2]), _ [fraOperations] = 2, Nz([txtNumber1]) - Nz([txtNumber2]), _ [fraOperations] = 3, Nz([txtNumber1]) * Nz([txtNumber2]), _ [fraOperations] = 4, Nz([txtNumber1]) / Nz([txtNumber2])) End Sub

5. Return to the form in Microsoft Access and switch it to Form View to test it:
    
   
6. Save and close the form