Microsoft Access Database Development With VBA

Introduction to Conditions

 

Logical Operators

 

Introduction

A comparison is an operation used to get the boolean result of two values, one checked against the other. Such a comparison is performed between two values of the same type. The Visual Basic language supports many operators to perform comparisons.

Equality

To compare two variables for equality, use the = operator. It can be illustrated as follows:

The comparison for equality

Here is an example:

Private Sub cmdBooleanVariable_Click()
    Dim IsFullTime As Boolean

    MsgBox "Is Employee Full Time? " & IsFullTime

    IsFullTime = True
    MsgBox "Is Employee Full Time? " & IsFullTime
End Sub

This would produce:

Boolean Variable

Boolean Variable

Inequality <>

To compare two values to know whether they are different, use the <> operator. It can illustrated as follows:

Variable1 <> Variable2

The comparison for inequality

Here is an example:

Public Function IsDifferent(ByVal Value1 As Integer, _
                            ByVal Value2 As Integer) As Boolean
    IsDifferent = Value1 <> Value2
End Function

Private Sub cmdBooleanVariable_Click()
    Dim a%, b%
    Dim Result As Boolean

    a% = 12: b% = 48
    Result = IsDifferent(a%, b%)

    MsgBox "The resulting comparison of 12 <> 48 is " & Result
End Sub

This would produce:

Boolean Variable

A Lower Value <

To find out whether one value is lower than another, use the < operator. It can be illustrated as follows:

Flowchart: Less Than

Here are two examples:

Private Sub cmdBooleanVariable_Click()
    Dim PartTimeSalary, ContractorSalary As Double
    Dim IsLower As Boolean
       
    PartTimeSalary = 20.15
    ContractorSalary = 22.48
    IsLower = PartTimeSalary < ContractorSalary

    MsgBox ("Part Time Salary:  " & PartTimeSalary & vbCrLf & _
            "Contractor Salary: " & ContractorSalary & vbCrLf & _
            "Is PartTimeSalary < ContractorSalary? " & IsLower)

    PartTimeSalary = 25.55
    ContractorSalary = 12.68
    IsLower = PartTimeSalary < ContractorSalary

    MsgBox ("Part Time Salary:  " & PartTimeSalary & vbCrLf & _
            "Contractor Salary: " & ContractorSalary & vbCrLf & _
            "Is PartTimeSalary < ContractorSalary? " & IsLower)
End Sub

This would produce:

True False
 

Equality and Lower Value <=

To find out if one value is lower than another, use the <= operator. It can be illustrated as follows:

Value1 <= Value2

Less than or equal to

Greater Value >

To find whether one value is greater than another, use the > operator. It can be illustrated as follows:

Value1 > Value2

Greater Than

Here is an example:

Private Sub cmdBooleanVariable_Click()
    Dim PartTimeSalary, ContractorSalary As Double
    Dim IsLower As Boolean

    PartTimeSalary = 20.15
    ContractorSalary = 22.48
    IsLower = PartTimeSalary > ContractorSalary

    MsgBox ("Part Time Salary:  " & PartTimeSalary & vbCrLf & _
            "Contractor Salary: " & ContractorSalary & vbCrLf & _
            "Is PartTimeSalary > ContractorSalary? " & IsLower)

    PartTimeSalary = 25.55
    ContractorSalary = 12.68
    IsLower = PartTimeSalary > ContractorSalary

    MsgBox ("Part Time Salary:  " & PartTimeSalary & vbCrLf & _
            "Contractor Salary: " & ContractorSalary & vbCrLf & _
            "Is PartTimeSalary > ContractorSalary? " & IsLower)
End Sub

This would produce:

True True

Greater or Equal Value >=

To find whether one value is greater than or equal to another, use the >= operator. It can be illustrated as follows:

Value1 >= Value2

Greater Than Or Equal

Here is a summary table of the logical operators we have studied:

 
Operator Meaning Example Opposite
= Equality to a = b <>
<> Not equal to 12 <> 7 =
< Less than 25 < 84 >=
<= Less than or equal to Cab <= Tab >
> Greater than 248 > 55 <=
>= Greater than or equal to Val1 >= Val2 <
 

The If...Then Statement

 

Introduction

The comparison operators we have reviewed above are used to know the state of two variables but they don't provide a mechanism to exploit the result. After getting the result of a comparison, to use it effectively, you can formulate a conditional 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:

If Condition

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 Detail_Click()
    Dim dNumber As Double
    
    dNumber = CDbl(txtNumber)
    
    If dNumber > 22 Then MsgBox "The number is greater than 22"
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 formula used is:

If Condition Then
    Statement
End If

The example above can be re-written as follows:

Private Sub Detail_Click()
    Dim dNumber As Double
    
    dNumber = CDbl(txtNumber)
    
    If dNumber > 22 Then
        MsgBox "The number is greater than 22"
    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 cmdCheck_Click()
    Dim dAge As Double
    
    dAge = CDbl(txtAge)
    
    If dAge >= 21 Then
        Caption = "Membership Category: Adult"
        MsgBox "The number is greater than 22."
        MsgBox "The member will be considered an adult."
    End If
End Sub

Introduction to Conditional Statements

Introduction to Conditional Statements

Practical LearningPractical Learning: Using the If...Then Statement

  1. Start Microsoft Access
  2. From the resources that accompany these lessons, open the Exercise3 database
  3. Open the Employment Application form in Design View and click the top text box
  4. In the Properties window, click the Event button and double-click On Lost Focus
  5. Click its ellipsis button Ellipsis 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
  6. In the Object combo box, select txtLastName
  7. In the Procedure combo box, select LostFocus
  8. 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
  9. Return to Microsoft Access and switch the form to Form View
  10. Click the top text box, type Julienne and press Tab. Notice that only the first name displays in the Full Name text box
     
    Using If...Then
  11. In the other text box, type Palace and Press Enter
     
    Using If...Then
  12. Close the form
  13. When asked whether you want to save it, click Yes

Using Logical Functions

In our introduction to Boolean values, we saw that the Visual Basic language provides many logical built-in functions such as IsEmpty(), IsNull(), or IsObject(). In most cases, to use these functions, you make them part of an If...Then conditional statement. Each one of these functions returns a Boolean value as true or false. Therefore, to effectually use these functions, you will include them in a statement that takes an expression or a field as argument and produces an logical result.

Practical LearningPractical Learning: Using the IsNumeric() Function

  1. From the resources that accompany these lessons, open the Exercise2 database
  2. In the Navigation Pane, double-click the Procedures form
  3. On the form, click the top Calculate button and notice that you receive a 94 error: Invalid use of Null
     
    Error 94
  4. Click Debug to open Microsoft Visual Basic and the Code Editor
  5. On the Standard toolbar of Microsoft Visual Basic, click the Stop button Stop
  6. Change the procedures of the module of the form as follows:
     
    Option Compare Database
    Option Explicit
    Private 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
  7. Return to Microsoft Access
  8. Click the Calculate buttons and notice that the errors are eliminated
  9. Enter some values in the text boxes and click the corresponding Calculate buttons
  10. Close the form
  11. When asked whether you want to save it, click Yes
 
 
 

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 case 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

The If...Then...ElseIf Statement

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

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

  1. Re-open the Exercise3 database that accompany these lessons
  2. In the Navigation Pane, right-click the Payroll form and click Design View:
     
    Georgetown Cleaning Services - Employee Payroll Design
     
  3. Right-click the Process It button and click Build Event...
  4. In the Choose Builder dialog box, double-click Code Builder
  5. 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
  6. In the Object combo box, select cmdClose
  7. Implement the event as follows:
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  8. Return to the form and switch it to Form View
  9. Test it with some values
     
    Georgetown Cleaning Services - Employee Payroll
  10. Close the form
  11. When asked whether you want to save it, click Yes

The Else Statement

 produce a true result. Therefore, in anticipation of such an 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 the NOT Operator

  1. From the resources that accompany these lessons, open the Clarksville Ice Cream1 database
  2. In the Navigation Pane, right-click IceCreamOrders and click Design View
  3. Right-click the Calculate button and click Build Event...
  4. On the Choose Builder dialog box, double-click Code Builder
  5. Implement the Click event as follows:
    Private Sub cmdCalculate_Click()
        Dim iScoops As Integer
        Dim PriceScoops As Double
        Dim PriceContainer As Double
        Dim PriceIngredient As Double
        Dim NumberOfScoops As Integer
        
        If IsNumeric(Scoops) Then iScoops = CInt(Scoops)
        
        If Container = "Cone" Then
            PriceContainer = 0.55
        ElseIf Container = "Cup" Then
            PriceContainer = 0.75
        Else
            PriceContainer = 1.75
        End If
        
        If iScoops = 1 Then
    	PriceScoops = 1.85
        ElseIf iScoops = 2 Then
            PriceScoops = 2.25
        Else
            PriceScoops = 3.25
        End If
        
        If Ingredient = "" Then
            PriceIngredient = 0#
        Else
            PriceIngredient = 0.75
        End If
        
        OrderTotal = PriceContainer + PriceScoops + PriceIngredient
    End Sub
  6. Return to Microsoft Access and switch the form to Form View
  7. Enter some values in the controls to process an order and click Calculate
     
    Clarksville Ice Cream
  8. Close the form
  9. When asked whether you want to save, click Yes

If...Then...Else-Based Function: IIf

To assist you with checking a condition and its alternative, the Visual Basic language provides a function named IIf. Its syntax is:

Public Function IIf( _
   ByVal Expression As Boolean, _ 
   ByVal TruePart As Variant, _ 
   ByVal FalsePart As Variant _ 
) As Variant

This function operates like an If...Then...Else condition. It takes three required arguments and returns a result of type Variant. This returned value will hold the result of the function.

The condition to check is passed as the first argument:

  • If that condition is true, the function returns the value of the TruePart argument and the last argument is ignored
  • If the condition is false, the first argument is ignored and the function returns the value of the second argument

As mentioned already, you can retrieved the value of the right argument and assign it to the result of the function. The expression we saw early can be written as follows:

Private Sub cmdFunction_Click()()
    Dim MemberAge As Integer
    Dim MemberCategory As String

    MemberAge = 16

    MemberCategory = IIf(MemberAge <= 18, "Teen", "Adult")

    MsgBox "Membership: " & MemberCategory
End Sub

This would produce the same result we saw earlier.

In the same, you can call as many IIf functions in the subsequent FalsePart sections as you judge necessary:

Public Function IIf( _
   ByVal Expression As Boolean, _ 
   ByVal TruePart As Object, _ 
   	Public Function IIf( _
   	    ByVal Expression As Boolean, _ 
   	    ByVal TruePart As Object, _ 
   	    	Public Function IIf( _
   	    	    ByVal Expression As Boolean, _ 
   	    	    ByVal TruePart As Object, _ 
   	    		Public Function IIf( _
   	    		    ByVal Expression As Boolean, _ 
   	    		    ByVal TruePart As Object, _ 
   	    		    ByVal FalsePart As Object _ 
			) As Object
		) As Object
	) As Object
) As Object

Here is an example:

Private Sub cmdImmediateIf_Click()
    Dim Score As Integer
    Dim Grade As String
    
    Score = InputBox("Enter Course Grade", "High School Grades", "0")
    
    Grade = IIf(Score >= 90, "A", IIf(Score >= 75, "B", IIf(Score >= 60, "C", IIf(Score >= 50, "D", "F"))))
    MsgBox "Your final grade is " & CStr(Grade)
End Sub

Managing Conditional Statements

 

Conditional Nesting

So far, we have learned to create normal conditional statements. Here is an example:

Private Sub cmdCondition_Click()
    Dim Number%

    Rem Request a number from the user
    Number% = InputBox("Enter a number that is lower than 5")

    Rem Find if the number is positive or 0
    If Number% >= 0 Then
        Rem If the number is positive, display it
        MsgBox Number%
    End If
End Sub

When this procedure executes, the user is asked to provide a number. If that number is positive, a message box displays it to the user. If the user enters a negative number, nothing happens. In a typical program, after validating a condition, you may want to take action. To do that, you can create a section of program inside the validating conditional statement. In fact, you can create a conditional statement inside of another conditional statement. This is referred to as nesting a condition. Any condition can be nested in another and multiple conditions can be included inside of another.

Here is an example where an If...Then condition is nested inside of another If...Then statement:

Private Sub cmdCondition_Click()
    Dim Number%

    Rem Request a number from the user
    Number% = InputBox("Enter a number that is lower than 5")

    Rem Find if the number is positive or 0
    If Number% >= 0 Then
        Rem If the number is positive, accept it
        If Number% < 12 Then
            MsgBox Number%
        End If
    End If
End Sub

The Goto Statement

The Goto statement allows a program execution to jump to another section of a procedure in which it is being used. In order to use the Goto statement, insert a name on a particular section of your procedure so you can refer to that name. The name, also called a label, is made of one word and follows the rules we have applied to names (the name can be anything), then followed by a colon ":". Here is an example:

Private Sub cmdCondition_Click()

    ' Do some thing(s) here

SomeLabelHere:
    ' Do some other thing(s) here
End Sub

After creating the label, you can process it. In the code before the label, you can do something. In that section, if a condition happens that calls for jumping to the label, then use a GoTo statement to send the flow to the corresponding label by typing the name of the label on the right side of GoTo. Here is an example:

Private Sub cmdCondition_Click()
    Dim Number%

    Rem Request a number from the user
    Number% = InputBox("Enter a number that is lower than 5")

    Rem Find if the number is positive or 0
    If Number% < 0 Then
        GoTo NegativeNumber
    Else
        Rem If the number is positive, display it
        MsgBox Number%
    End If
    
NegativeNumber:
    MsgBox "You entered a negative number"
End Sub

In the same way, you can create as many labels as you judge them necessary in your code and refer to them when you want. The name must be unique in its scope. This means that each label must have a unique name in the same procedure. Here is an example with various labels:

Private Sub cmdCondition_Click()
    Dim Answer As Byte

    Answer = InputBox(" -=- Multiple Choice Question -=-" & vbCrLf & _
                      "To create a constant in your code, " & _
                      "you can use the Constant keyword" & vbCrLf & _
                      "Your choice (1=True/2=False)? ")


    If Answer = 1 Then GoTo Wrong
    If Answer = 2 Then GoTo Right

Wrong:
    MsgBox"Wrong: The keyword used to create a constant is Const"
    GoTo Leaving

Right:  MsgBox"Right: Constant is not a keyword"

Leaving:

End Sub

Here is an example of executing the program with Answer = 1:

Go To

Go To

Here is another example of executing the same program with Answer = 2:

Go To

Go To

Negating a Conditional Statement

So far, we have learned to write a conditional statement that is true or false. You can reverse the true (or false) value of a condition by making it false (or true). To support this operation, the Visual Basic language provides an operator called Not. Its formula is:

Not Expression

When writing the statement, type Not followed by a logical expression. The expression can be a simple Boolean expression. Here is an example:

Private Sub cmdCondition_Click()
    Dim IsMarried As Boolean

    MsgBox "Is Married: " & IsMarried
    MsgBox "Is Married: " & Not IsMarried 
End Sub

This would produce:

Not Not

In this case, the Not operator is used to change the logical value of the variable. When a Boolean variable has been "notted", its logical value has changed. If the logical value was True, it would be changed to False and vice versa. Therefore, you can inverse the logical value of a Boolean variable by "notting" or not "notting" it.

Now consider the following:

Private Sub cmdCondition_Click()
    Dim IsMarried As Boolean
    Dim TaxRate As Double

    TaxRate = 33.0

    MsgBox "Tax Rate: " & TaxRate & "%"

    IsMarried = True
    If IsMarried = True Then
        TaxRate = 30.65

        MsgBox "Tax Rate: " & TaxRate & "%"
    End If
End Sub

This would produce:

NOT If Condition

Probably the most classic way of using the Not operator consists of reversing a logical expression. To do this, you precede the logical expression with the Not operator. Here is an example:

Private Sub cmdCondition_Click()
    Dim IsMarried As Boolean
    Dim TaxRate As Double

    TaxRate = 33.0
    MsgBox "Tax Rate: " & TaxRate & "%"

    IsMarried = True

    If Not IsMarried Then
        TaxRate = 30.65
        MsgBox "Tax Rate: " & TaxRate & "%"
    End If
End Sub

This would produce:

NOT

In the same way, you can negate any logical expression.

Practical LearningPractical Learning: Using the NOT Operator

  1. Re-open the Exercise2 database
  2. In the Navigation Pane, right-click Assignment and click Design View
  3. Right-click the small button on the right side of the Last Name text box and click Build Event...
  4. Change the Click event 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 = LCase(Left(txtFirstName, 1) & txtLastName)
        ' Create a full name as the first name followed by the last name
        strFullName = txtFirstName & " " & strMiddleInitial & txtLastName
        
        txtFullName = strFullName
        txtUsername = strUsername
    End Sub
  5. Return to Microsoft Access
  6. Switch the form to Form View
  7. Enter a first name, a middle initial, and a last name in the designated text box
  8. Click the button on the right side of Last Name
  9. Close the form
  10. When asked whether you want to save it, click Yes

Conditional Statements and Functions

 

Introduction

We know that a function is used to perform a specific assignment and produce a result. Here is an example:

Private Function SetMembershipLevel$()
    Dim MemberAge%

    MemberAge% = InputBox("Enter the Member's Age")

    SetMembershipLevel$ = ""
End Function

When performing its assignment, a function can encounter different situations, some of which would need to be checked for truthfulness or negation. This means that conditional statements can assist a procedure with its assignment.

Conditional Returns

A function is meant to return a value. Sometimes, it will perform some tasks whose results would lead to different results. A function can return only one value (we saw that, by passing arguments by reference, you can make a procedure return more than one value) but you can make it render a result depending on a particular behavior. If a function is requesting an answer from the user, since the user can provide different answers, you can treat each result differently. Consider the following function:

Private Function SetMembershipLevel$()
    Dim MemberAge%

    MemberAge% = InputBox("Enter the Member's Age")

    If MemberAge% < 18 Then
        SetMembershipLevel$ = "Teen"
    ElseIf MemberAge% < 55 Then
        SetMembershipLevel$ = "Adult"
    End If
End Function

Private Sub cmdFunction_Click()
    Dim Membership$

    Membership$ = SetMembershipLevel$
    MsgBox "Membership: " & Membership$
End Sub

At first glance, this function looks fine. The user is asked to provide a number. If the user enters a number less than 18 (excluded), the function returns Teen. Here is an example of running the program:

Conditional Statements and Functions

Conditional Statements and Functions

If the user provides a number between 18 (included) and 55, the function returns Adult. Here is another example of running the program:

Conditional Statements and Functions

Conditional Statements and Functions

What if there is an answer that does not fit those we are expecting? The values that we have returned from the function conform only to the conditional statements and not to the function. Remember that in an If Condidion Statement, the Statement executes only if the Condition is true. Here is what will happen. If the user enters a number higher than 55 (excluded), the function will not execute any of the returned statements. This means that the execution will reach the End Function line without encountering a return value. This also indicates to the compiler that you wrote a function that is supposed to return a value, but by the end of the method, it didn't return a value. Here is another example of running the program:

Conditional Statements and Functions

Conditional Statements and Functions

To solve this problem, you have various alternatives. If the function uses an If...Then condition, you can create an Else section that embraces any value other than those validated previously. Here is an example:

Private Function SetMembershipLevel$()
    Dim MemberAge%

    MemberAge% = InputBox("Enter the Member's Age")

    If MemberAge% < 18 Then
        SetMembershipLevel$ = "Teen"
    ElseIf MemberAge% < 55 Then
        SetMembershipLevel$ = "Adult"
    Else
        SetMembershipLevel$ = "Senior"
    End If
End Function

Private Sub cmdFunction_Click()
    Dim Membership$

    Membership$ = SetMembershipLevel$
    MsgBox "Membership: " & Membership$
End Sub

This time, the Else condition would execute if no value applies to the If or ElseIf conditions and the compiler would not produce a warning. Here is another example of running the program:

Conditional Statements and Functions

Conditional Statements and Functions

An alternative is to provide a last return value just before the End Function line. In this case, if the execution reaches the end of the function, it would still return something but you would know what it returns. This would be done as follows:

Private Function SetMembershipLevel$()
    Dim MemberAge%

    MemberAge% = InputBox("Enter the Member's Age")

    If MemberAge% < 18 Then
        SetMembershipLevel$ = "Teen"
    ElseIf MemberAge% < 55 Then
        SetMembershipLevel$ = "Adult"
    End If

    SetMembershipLevel$ = "Senior"
End Function

If the function uses an If condition, both implementations would produce the same result.

 
 
   
 

Previous Copyright © 2002-2013 FunctionX, Inc. Next