Conditional Statements

 Logical Operators

 Introduction
 One of the goals of computer programming is telling the computer what to do when something occurs, and how to do it. This is performed by setting conditions, examining them and stating what decisions the computer should make. To perform the necessary conditions, you have two main options: Microsoft Access or Microsoft Visual Basic. Microsoft Access is equipped with a series of operators and functions destined to perform various operations. To use a condition in Microsoft Access, if you know the structure of the conditional statement, you can write it after typing an assignment operator. Because most conditions in Microsoft Access are in the form of functions, we will study them later on. For now, we will learn how to write conditions in the Microsoft Visual Basic language.

Microsoft Visual Basic comes with a lot of conditional statements for almost any situation your computer can encounter. As the application developer, it is up to you to anticipate these situations and make your program act accordingly.

A comparison is performed between two values of the same type. For example, you can compare two numbers, two characters, or the names of two cities. On the other hand, a comparison between two disparate values doesn't bear any meaning. For example, it is difficult to compare a telephone number and somebody's grand-mother name, or a music category and the distance between two points. Like the arithmetic operations, the comparison operations are performed on two values. Unlike arithmetic operations where results are varied, a comparison produces only one of two results. The result can be a logical True or False.

To perform the necessary comparisons, Microsoft Access and Visual Basic use a series of logical operators and constants.

 The Comparison for Equality =

To compare two variables for equality, use the = operator. Its formula is:

`Value1 = Value2`

The equality operation is used to find out whether two variables (or one variable and a constant) hold the same value. From our formula, the value of Value1 would be compared with the value of Value2. If Value1 and Value2 hold the same value, the comparison produces a True result. If they are different, the comparison renders false or 0. After performing the comparison and finding out its result, you can carry an assignment. This can be illustrated as follows:

 The Logical Not Operator

When a variable is declared and receives a value (this could be done through initialization or a change of value) in a program, it becomes alive. When a variable is not being used or is not available for processing (in visual programming, it would be considered as disabled) to make a variable (temporarily) unusable, you can nullify its value. To render a variable unavailable during the evolution of a program, apply the logical Not operator. Its formula is:

`Not Value`

There are two main ways you can use the logical Not operator. As we will learn when studying conditional statements, the most classic way of using the logical Not operator is to check the state of a variable.

When a variable holds a value, it is "alive". To make it not available, you can "not" it. When a variable has been "notted", its logical value has changed. If the logical value was True, it would be changed to False. Therefore, you can inverse the logical value of a variable by "notting" or not "notting" it.

 For Inequality <>

As opposed to equality, Microsoft Visual Basic provides an operator used to compare two values for inequality, which is <>. Its formula is:

`Value1 <> Value2`

<> is a binary operator (like all logical operators except the logical Not, which is a unary operator) that is used to compare two values. The values can come from two variables as in Variable1 <> Variable2. Upon comparing the values, if both variables hold different values, the comparison produces a True value. Otherwise, the comparison renders False or a null value. This can be illustrated as follows:

The inequality is obviously the opposite of the equality.

 A Lower Value <

To find out whether one value is lower than another, use the < operator. Its formula is:

`Value1 < Value2`

The value held by Value1 is compared to that of Value2. As it would be done with other operations, the comparison can be made between two variables, as in Variable1 < Variable2. If the value held by Variable1 is lower than that of Variable2, the comparison produces a True. This can be illustrated with the following:

 Combining Equality and Lower Value <=

The previous two operations can be combined to compare two values. This allows you to know if two values are the same or if the first is less than the second. The operator used is <= and its formula is:

`Value1 <= Value2`

The <= operation performs a comparison as any of the last two. If both Value1 and Value2 hold the same value, the result is true. If the left operand, in this case Value1, holds a value lower than the second operand, in this case Value2, the result is still true. This can be illustrated as follows:

The result of this comparison is false only if the value of the left operand is strictly higher than that of the right operand.

 A Greater Value >

When two values of the same type are distinct, one of them is usually higher than the other. Microsoft Visual Basic provides a logical operator that allows you to find out if one of two values is greater than the other. The operator used for this operation is the > symbol. Its formula is:

`Value1 > Value2`

Both operands, in this case Value1 and Value2, can be variables or the left operand can be a variable while the right operand is a constant. If the value on the left of the > operator is greater than the value on the right side or a constant, the comparison produces a True value. Otherwise, the comparison renders False or null. This would illustrated as follows:

The > and the <= are opposite operators.

 Greater or Equal Value >=

The greater than or the equality operators can be combined to produce an operator as follows: >=. This is the "greater than or equal to" operator. Its formula is:

`Value1 >= Value2`

A comparison is performed on both operands: Value1 and Value2. If the value of Value1 and that of Value2 are the same, the comparison produces a True value. If the value of the left operand is greater than that of the right operand, the comparison still produces True. If the value of the left operand is strictly less than the value of the right operand, the comparison produces a False result:

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

 Operator Meaning Example Opposite = Equality to a = b Not Not 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 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
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 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 Learning: Using the IsNull() Function
1. Open the Exercise2 application. 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 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```
 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
FormFooter.BackColor = 14511872
Else
Detail.BackColor = 13034239
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 Learning: Using If...Then...ElseIf
1. From the Forms section of the Database window, right-click the Payroll form and click Design View:

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```
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```
10. Test it with some values

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"
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
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: 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
 Looping and Counting

 Do While...Loop

Loops are used to repeat an action and they use the Do keyword in combination with other keywords to perform and conditional statement. There are various variations of the Do loops.

The syntax of the Do While loop is:

```Do While Condition
Statement(s)
Loop```

The program will first test the Condition. If the Condition is true, the program would execute the Statement or Statements and go back to the Do While statement and test the condition again. This expression will execute the Statement or statements AS LONG AS the Condition is true, as many times as the Condition will be visited and found true. If the Condition is false, the program will skip the Do While statement and not execute any.

Here is an example:

```Private Sub cmdCounter_Click()
Dim Number As Integer

Do While Number < 46
MsgBox CStr(Number)
Number = Number + 4
Loop

MsgBox "Counting Stopped at " & CStr(Number)
End Sub```
 Do...Loop While

Since the Do While statement tests the Condition first before executing the Statement, sometimes you will want the program to execute the Statement first, then go back and test the Condition. Visual Basic offers a reverse to the syntax, which is:

```Do
Statement(s)
Loop While Condition```

In this case, Visual Basic will execute the Statement or Statements first, then it will test the Condition. If the Condition is true, the program will execute the Statement again. The program will continue this examination-execution as long as the Condition is true. The big difference here is that even if the Condition is false, the program will have executed the Condition at least once.

Here is an example:

```Private Sub cmdCounter_Click()

Do
Answer = CStr(InputBox("Are we there yet (1=Yes/0=No)?", "Counter", "1"))

MsgBox "Wonderful, we have arrived"
End Sub```

Here is an example of running the code:

 Do Until...Loop

An alternative to the Do While loop is the Do Until loop. Its syntax is:

```Do Until Condition
Statement(s)
Loop```

This loop will first examine the Condition, instead of examining whether the Condition is true, it will test whether the Condition is false.

Here is an example:

```Private Sub cmdCounter_Click()

Answer = InputBox("Are we there yet (1=Yes/0=No)?", "Counter", "1")
Loop

MsgBox "Wonderful, we have arrived"
End Sub```
 Do...Loop Until

The other side of the Do Until loop would execute the Statement first, then it would examine the Condition. The syntax used is:

```Do
Statement(s)
Loop Until Condition```
 For...Next

If you don't know how many times a statement needs to be executed, you can use one of the Do loops. But whenever you want to control how many times a statement should be executed, the For...Next loop offers a better alternative. The syntax used is:

```For Counter = Start To End
Statement(s)
Next```

Used for counting, the For...Next loop begins counting at the Start point. Then it examines whether the current value (after starting to count) is greater than End; if that's the case, the program exits the loop. It then executes the Statement or Statements. Next, it increments the value of Counter by 1 and examines the condition again. This process goes on until Counter = End.

The syntax above will increment the counting by 1 at the end of each statement. If you want to control how the incrementing processes, you can set your own, using the Step option. Here is the syntax you would use:

```For Counter = Start To End Step Increment
Statement(s)
Next Counter```

You can set the incrementing value to your choice. If the value of Increment is positive, the Counter will be added its value. This means that you can give it a negative value, in which case the Counter would be subtracted the set value.

 Counting and Looping: For...Each

Since the For...Next loop is used to execute a group of statements based on the current result of the loop counting from Start to End, an alternative is to state various steps in the loop and execute a group of statements for each one of the elements in the group. This is mostly used when dealing with a collection of items.

The syntax used is:

```For Each Element In Group
Statement(s)
Next Element```

The loop will execute the Statement or Statement(s) for each Element in the Group.