1. Re-open the Exercise3 database that accompany these lessons
2. In the Navigation Pane, right-click the Payroll form and click Design View:
    
   
    
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
    
   
10. Close the form
11. When asked whether you want to save it, click Yes

 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.

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
    
   
8. Close the form
9. When asked whether you want to save, click Yes

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

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

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

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:

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:

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:

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

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

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.

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:

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

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:

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:

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.