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.
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:
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:
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.
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.
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:
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.
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.
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:
The comparison operators we have reviewed above are used to know the state of two values but they don't provide a mechanism to exploit there result. After getting the result of a comparison, to use it effectively, you can formulate a condition statement. Microsoft Access and Microsoft Visual Basic support this through various keywords and functions.
The If...Then statement examines the truthfulness of an expression. Structurally, its formula is:
If Condition Then Statement
The program will examine the Condition. This condition can be a simple expression or a combination of expressions. If the Condition is true, then the program will execute the Statement. This can be illustrated as follows:
There are two ways you can use the If...Then statement. If the conditional expression is short enough, you can write it on one line using the following formula:
If Condition Then Statement
In the following example, if the text box named txtGender of a form displays Male, the background color of the Detail section would be colored in light blue:
Private Sub Form_Current() If txtGender = "Male" Then Detail.BackColor = 16772055 End Sub
If there are many statements to execute as a truthful result of the condition, you should write the statements on subsequent lines. Of course, you can use this technique even if the condition you are examining is short. If then you use the Statement on a different line, you must terminate the conditional statement with the End If expression. The formual used is:
If Condition Then Statement End If
The example above can be re-written as follows:
Private Sub Form_Current() If txtGender = "Male" Then Detail.BackColor = 16772055 End If End Sub
If the condition needs to cover many lines of code, the syntax to apply is:
If Condition Then Statement1 Statement2 Statement_n End If
Here is an example:
Private Sub Form_Current() If Gender = "Male" Then Detail.BackColor = 16772055 FormHeader.BackColor = 16752478 FormFooter.BackColor = 14511872 End If End Sub
Microsoft Access doesn't use conditionals statements like traditional computer languages do. It relies on special condition-oriented functions to perform the same operations. One of these functions is called Nz.
The Nz() function is used to check the value of an expression or a control. Its syntax is:
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.
The IsEmpty() function checks whether an existing variable has been initialized or not. The syntax of this function is
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.
IsNull() is a Boolean function that checks whether an expression holds no recognizable value. Its syntax is:
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.
To check whether an expression can produce a numeric value, you can call the Boolean IsNumeric() function whose syntax is:
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.
The If...Then statement offers only one alternative: to act if the condition is true. Whenever you would like to apply an alternate expression in case the condition is false, use the If...Then...Else statement. The formula of this statement is:
If ConditionIsTrue Then Statement1 Else Statement2 End If
The condition, in this ConditionIsTrue, would be examined. If it produces a true result, then the first statement, in this case Statement1, would be executed. If the condition (ConditionIsTrue) is false, the second statement, in this case Statement2, would be executed.
Here is an example:
Private Sub Form_Current() If Gender = "Male" Then Detail.BackColor = 16772055 Else Detail.BackColor = 13034239 End If End Sub
If any of the expressions needs more than one line of code to have a complete implementation, you can include it in the needed section before the end of the section. Here is an example:
Private Sub Form_Current() If Gender = "Male" Then Detail.BackColor = 16772055 FormHeader.BackColor = 16752478 FormFooter.BackColor = 14511872 Else Detail.BackColor = 13034239 FormHeader.BackColor = 7452927 FormFooter.BackColor = 29670 End If End Sub
Because Microsoft Access doesn't support conditional statements such as the If...Then...Else combination, its relies on a function such as IIf() but, of course, this function can be used in any database or Microsoft Visual Basic expression. The syntax of the IIf() function is:
IIf(Condition, WhatToDoIfTrue, WhatToDoIfFalse)
When called, this function starts by checking the condition. Therefore, always make sure that you provide a condition that can produce a True or a False result. If the Condition evaluates to True, the expression in the second argument is applied. If the Condition produces False, the expression of the last argument applies.
The If...Then...ElseIf statement acts like the If...Then...Else, except that it offers as many choices as necessary. The formula is:
If Condition1 Then Statement1 ElseIf Condition2 Then Statement2 ElseIf Condition_n Then Statement_n End If
The program will first examine the first condition, in this case Condition1. If Condition1 is true, then the program would execute the first statement, in this case Statment1, and stop examining conditions. But if Condition1 is false, then the program would examine Condition2 and act accordingly. Whenever a condition is false, the program would continue examining the conditions until it finds one that is true. Once a true condition has been found and its statement executed, the program would terminate the conditional examination at End If.
The above syntax pre-supposes that at lease one of the conditions would produce a true result. Sometimes, regardless of how many conditions you use, it is possible that none of them would produce a true result. Therefore, in anticipation of such occurrence, you should provide an alternate statement that would embrace any condition that doesn't fit in the possible true results. This is done by combining an If...Then...Else and an If...Then...ElseIf statements. The resulting syntax to use is:
If Condition1 Then Statement1 ElseIf Condition2 Then Statement2 ElseIf Condition3 Then Statement3 Else Statement_False End If
In this case, if neither of the If and ElseIfs conditions was validated, then the last statement, in this case Statement_False, would execute.
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
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.
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)
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
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() Dim Answer As String Do Answer = CStr(InputBox("Are we there yet (1=Yes/0=No)?", "Counter", "1")) Loop While Answer <> "1" MsgBox "Wonderful, we have arrived" End Sub
Here is an example of running the code:
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() Dim Answer As String Do Until (Answer = "1") Answer = InputBox("Are we there yet (1=Yes/0=No)?", "Counter", "1") Loop MsgBox "Wonderful, we have arrived" End Sub
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
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.
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.
|Previous||Copyright © Yevol, 2007||Next|