Home

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
 

If...Then-Oriented Functions: Nz()

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.

Practical LearningPractical Learning: Using the Nz() Function

  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
 

If...Then-Oriented Functions: IsEmpty()

The IsEmpty() function checks whether an existing variable has been initialized or not. The syntax of this function is

IsEmpty(Expression)

This function checks the Expression argument. If the Expression argument holds a value, then the IsEmpty() function returns False value. If the Expression argument doesn't hold a value, for example if it has not (yet) been initialized, then the IsEmpty() function return True.

 

If...Then-Oriented Functions: IsNull()

IsNull() is a Boolean function that checks whether an expression holds no recognizable value. Its syntax is:

IsNull(Expression)

When called, this function evaluates the value of the Expression argument. If the argument holds a null value, then the IsNull() function returns True. If the Expression can produce a recognizable value, then the function returns False.

 

Practical LearningPractical Learning: Using the IsNull() Function

  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

If...Then-Oriented Functions: IsNumeric()

To check whether an expression can produce a numeric value, you can call the Boolean IsNumeric() function whose syntax is:

IsNumeric(Expression)

This function is used to evaluate the Expression argument. If the argument can produce a recognizable numeric value, the IsNumeric() function produces a True value. If an evaluation of the Expression produces a value that is not clearly a number, then the function returns False.

 

Practical LearningPractical Learning: Using the IsNumeric() Function

  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...Else Statement

 

Introduction

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
 

If...Then...Else-Related Functions: IIf

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

 

Introduction

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 LearningPractical Learning: Using If...Then...ElseIf

  1. From the Forms section of the Database window, right-click the Payroll form and click Design View:
     
    Georgetown Cleaning Services - Employee Payroll Design
     
  2. Right-click the Process It button and click Build Event...
  3. In the Choose Builder dialog box, double-click Code Builder
  4. Implement the event as follows:
     
    Private Sub cmdProcessIt_Click()
        Dim monday1 As Double
        Dim tuesday1 As Double
        Dim wednesday1 As Double
        Dim thursday1 As Double
        Dim friday1 As Double
        Dim saturday1 As Double
        Dim sunday1 As Double
        Dim monday2 As Double
        Dim tuesday2 As Double
        Dim wednesday2 As Double
        Dim thursday2 As Double
        Dim friday2 As Double
        Dim saturday2 As Double
        Dim sunday2 As Double
        Dim totalHoursWeek1 As Double
        Dim totalHoursWeek2 As Double
    
        Dim regHours1 As Double
        Dim regHours2 As Double
        Dim ovtHours1 As Double
        Dim ovtHours2 As Double
        Dim regAmount1 As Currency
        Dim regAmount2 As Currency
        Dim ovtAmount1 As Currency
        Dim ovtAmount2 As Currency
        
        Dim regularHours As Double
        Dim overtimeHours As Double
        Dim regularAmount As Currency
        Dim overtimeAmount As Currency
        Dim totalEarnings As Currency
    
        Dim hourlySalary As Currency
    
        ' Retrieve the hourly salary
        hourlySalary = CDbl(Me.txtHourlySalary)
        ' Retrieve the time for each day
        ' First Week
        monday1 = CDbl(Me.txtMonday1)
        tuesday1 = CDbl(Me.txtTuesday1)
        wednesday1 = CDbl(Me.txtWednesday1)
        thursday1 = CDbl(Me.txtThursday1)
        friday1 = CDbl(Me.txtFriday1)
        saturday1 = CDbl(Me.txtSaturday1)
        sunday1 = CDbl(Me.txtSunday1)
        
        ' Second Week
        monday2 = CDbl(Me.txtMonday2)
        tuesday2 = CDbl(Me.txtTuesday2)
        wednesday2 = CDbl(Me.txtWednesday2)
        thursday2 = CDbl(Me.txtThursday2)
        friday2 = CDbl(Me.txtFriday2)
        saturday2 = CDbl(Me.txtSaturday2)
        sunday2 = CDbl(Me.txtSunday2)
        
        ' Calculate the total number of hours for each week
        totalHoursWeek1 = monday1 + tuesday1 + wednesday1 + thursday1 + _
                          friday1 + saturday1 + sunday1
        totalHoursWeek2 = monday2 + tuesday2 + wednesday2 + thursday2 + _
                          friday2 + saturday2 + sunday2
    
        ' The overtime is paid time and half
        Dim ovtSalary As Double
        ovtSalary = hourlySalary * 1.5
    
        ' If the employee worked under 40 hours, there is no overtime
        If totalHoursWeek1 < 40 Then
            regHours1 = totalHoursWeek1
            regAmount1 = hourlySalary * regHours1
            ovtHours1 = 0
            ovtAmount1 = 0
        ' If the employee worked over 40 hours, calculate the overtime
        ElseIf totalHoursWeek1 >= 40 Then
            regHours1 = 40
            regAmount1 = hourlySalary * 40
            ovtHours1 = totalHoursWeek1 - 40
            ovtAmount1 = ovtHours1 * ovtSalary
        End If
        
        If totalHoursWeek2 < 40 Then
            regHours2 = totalHoursWeek2
            regAmount2 = hourlySalary * regHours2
            ovtHours2 = 0
            ovtAmount2 = 0
        ElseIf totalHoursWeek2 >= 40 Then
            regHours2 = 40
            regAmount2 = hourlySalary * 40
            ovtHours2 = totalHoursWeek2 - 40
            ovtAmount2 = ovtHours2 * ovtSalary
        End If
        
        regularHours = regHours1 + regHours2
        overtimeHours = ovtHours1 + ovtHours2
        regularAmount = regAmount1 + regAmount2
        overtimeAmount = ovtAmount1 + ovtAmount2
        totalEarnings = regularAmount + overtimeAmount
    
        Me.txtRegularHours = regularHours
        Me.txtOvertimeHours = overtimeHours
        Me.txtRegularAmount = CCur(regularAmount)
        Me.txtOvertimeAmount = CCur(overtimeAmount)
    
        Me.txtNetPay = CCur(totalEarnings)
    End Sub
  5. Return to the form
  6. Right-click the Close button and click Build Event...
  7. In the Choose Builder dialog box, double-click Code Builder
  8. Implement the event as follows:
     
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  9. Return to the form and switch it to Form View
  10. Test it with some values
     
    Georgetown Cleaning Services - Employee Payroll
  11. Close the form
  12. When asked whether you want to save it, click Yes
 

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 Practical Learning: Using the Choose Function

  1. From the Forms section of the Database window, right-click the Compound Interest form and click Design View:
     
  2. On the Standard toolbar, click the Code button
  3. In the Object combo box, select cmdCalculate and implement its Click event as follows:
     
    Private Sub cmdCalculate_Click()
        Dim Principal As Currency
        Dim InterestRate As Double
        Dim InterestEarned As Currency
        Dim FutureValue As Currency
        Dim RatePerPeriod As Double
        Dim Periods As Integer
        Dim CompoundType As Integer
        Dim i As Double
        Dim n As Integer
        
        Principal = CCur(txtPrincipal)
        InterestRate = CDbl(txtInterestRate)
        
        CompoundType = Choose([fraFrequency], 12, 4, 2, 1)
        
        Periods = CInt(txtPeriods)
        i = InterestRate / CompoundType
        n = CompoundType * Periods
        RatePerPeriod = InterestRate / Periods
        FutureValue = Principal * ((1 + i) ^ n)
        InterestEarned = FutureValue - Principal
        
        txtInterestEarned = CStr(InterestEarned)
        txtAmountEarned = CStr(FutureValue)
    End Sub
  4. Return to Microsoft Access and switch the form to Form View to test it
     
  5. Close the form
  6. When asked whether you want to save it, click Yes
 

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:

Switch(Expression1, What To Do If Expression1 Is True,
       Expression2, What To Do If Expression2 Is True,
       Expression_n, What To Do If Expression_n Is True)

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:

Switch(Expression1, What To Do If Expression1 Is True,
       Expression2, What To Do If Expression2 Is True,
       Expression_n, What To Do If Expression_n Is True,
       True, What To Do With A False Expression)
   

Practical Learning 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
 

Previous Copyright © 2005-2010 FunctionX, Inc. Next