Operations on Variables 

Introduction 
An operation is at least one value combined with a symbol to produce a new value. A more complex operation can involve more than one value and possibly more than one symbol. A value involved in an operation is called an operand. A symbol involved in an operation is called an operator.
The assignment operation is used to make a copy of a value, an expression, or the content of a control and give the copy to another field or expression. The assignment operation is performed with the = sign. 
For example, suppose you have a field that displays a first name and that field is called FirstName. If you want that first name to display in another field, with this new field named, in the new field you could type: =FirstName On the other hand, you can use the assignment operator to give a value to a declared variable. Here is an example: Private Sub Form_Load() Dim NumberOfTracks As Integer NumberOfTracks = 16 End Sub When the assignment operator is provided to a variable as a starting value for the variable, this is referred to as initializing the variable.
We saw earlier that you could declare a variable based on a builtin object of VBA. To specify the particular object you are referring to, you can (must) use the Set operator to assign an existing object to your variable. This would be done as follows: dim ctlFirstName as Control Set ctlFirstName = TextBox
Doublequotes are used to display a string. First... A string is an empty space, a character, or a group of characters that you type or provide to a control and you want this character or this group of characters to be considered "as is". In other words, the expression or the control that receives the string should keep it or them the way you supplied it or them. A string can be an empty space or one character, such as $ or w; a group of characters, like home or Manchester United or Verbally speaking, I mean… Ah forget it. Most of the time, you will want the program to keep this character or group of characters exactly the way you or the user supplied them. In order to let the program know that this is a string, you must enclose it in double quotes. From our examples, our strings would be "$", "w", "home", "Manchester United", and "Verbally speaking, I mean… Ah forget it". To assign a string to an expression or a field, use the assignment operator as follows: = "Manchester United" In the same way, to initialize a variable with a string , use the assignment operator. Here is an example: Private Sub Form_Load() Dim Address As String Address = "12404 Lockwood Drive Apt D4" End Sub
The & operator is used to append two strings, the contents of two controls, or expressions; this is considered as concatenating them. For example, it could allow you to concatenate a first name and a last name, producing a full name. The general formula of the concatenation operator is expressed as: Value1 & Value2 To display a concatenated expression, use the assignment operator. To assign a concatenated expression to a variable, use the assignment operator the same way. Here is an example: Private Sub Form_Load() Dim FirstName, LastName As String Dim FullName As String FirstName = "Francis " LastName = "Pottelson" FullName = FirstName & LastName Text0 = FullName End Sub To concatenate more than two expressions, you can use as many & operators between any combination of two expressions as necessary. After concatenating the expressions or values, you can assign the result to another value or expression using the assignment operator. The syntax used is: =Value1 & " " & Value2 Examples:

The Negation Operator  
In mathematics, an integer such as 120 or a double floating number such as 98.005 is qualified as positive; that is, it is considered greater than 0. If a number is less than 0, to express it, you can add the  sign on the left side of the number. Examples are 5502 or 240.65. The  sign signifies that the number is negative. A variable or an expression can also be represented as negative by prefixing it with a  sign. Examples are Distance or NbrOfPlayers. To initialize a variable with a negative value , use the assignment operator. Here is an example: Private Sub Form_Load() Dim NumberOfTracks As Byte Dim Temperature As Integer NumberOfTracks = 16 Temperature = 94 End Sub 
The Addition: + 
The addition is used to add one value or expression to another. It is performed using the + symbol and its formula is: Value1 + Value2 The addition allows you to add two numbers such as 12 + 548 or 5004.25 + 7.63 After performing the addition, you get a result. You can provide such a result to another variable or control. This is done using the assignment operator. The formula used would be: = Value1 + Value2 
Practical Learning: Using the Addition 

The Subtraction:  
The subtraction is performed by retrieving one value from another value. This is done using the  symbol. The syntax used is: Value1  Value2 The value of Value1 is subtracted from the value of Value2. After the operation is performed, a new value results. This result can be used in any way you want. For example, you can display it in a control using the assignment operator as follows: = Value1  Value2 
The Multiplication: * 
The multiplication allows adding one value to itself a certain number of times, set by the second value. The multiplication is performed with the * sign which is typed with Shift + 8. Here is an example: Value1 * Value2 During the operation, Value1 is repeatedly added to itself, Value2 times. The result can be assigned to another value or displayed in a control as follows: = Value1 * Value2 
Practical Learning: Using the Multiplication 

The Integer Division: \ 
Dividing an item means cutting it in pieces or fractions of a set value. For example, when you cut an apple in the middle, you are dividing it in 2 pieces. If you cut each one of the resulting pieces, you will get 4 pieces or fractions. This is considered that you have divided the apple in 4 divisions. Therefore, the division is used to get the fraction of one number in terms of another. Microsoft Visual Basic provides two types of results for the division operation. If you want the result of the operation to be a natural number, called an integer, use the backlash operator "\" as the divisor. Here is an example: Value1 \ Value2 This operation can be performed on two types of valid numbers, with or without decimal parts. After the operation, the result would be a natural number. The result of the operation can be assigned to another value. It can also be displayed in a control using the assignment operator: = Value1 \ Value2 
The Division: / 
The second type of division results in a decimal number. It is performed with the forward slash "/". Its syntax is: Value1 / Value2 After the operation is performed, the result is a decimal number. The result of either operation can be assigned to another value. It can also be displayed in a control using the assignment operator: = Value1 / Value2 
The Exponentiation: ^ 
Exponentiation is the ability to raise a number to the power of another number. This operation is performed using the ^ operator (Shift + 6). It uses the following mathematical formula: y^{x} In Microsoft Visual Basic (and Microsoft Access), this formula is written as: y^x and means the same thing. Either or both y and x can be values or expressions, but they must carry valid values that can be evaluated. When the operation is performed, the value of y is raised to the power of x. You can display the result of such an operation in a field using the assignment operator as follows: =y^x You can also assign the operation to an expression as follows: Total = y^x 
Practical Learning: Using the Exponentiation Operator 

The Remainder Operator: Mod 
The division operation gives a result of a number with or without decimal values, which is fine in some circumstances. Sometimes you will want to get the value remaining after a division renders a natural result. Imagine you have 26 kids at a football (soccer) stadium and they are about to start. You know that you need 11 kids for each team to start. If the game starts with the right amount of players, how many will seat and wait? The remainder operation is performed with keyword Mod. Its syntax is: Value1 Mod Value2 The result of the operation can be used as you see fit or you can display it in a control using the assignment operator as follows: = Value1 Mod Value2

The Parentheses Operators: () 
Parentheses are used in two main circumstances: in an event (or procedures, as we will learn) or in an operation. The parentheses in an operation help to create sections in an operation. This regularly occurs when more than one operators are used in an operation. Consider the following operation: 8 + 3 * 5 The result of this operation depends on whether you want to add 8 to 3 then multiply the result by 5 or you want to multiply 3 by 5 and then add the result to 8. Parentheses allow you to specify which operation should be performed first in a multioperator operation. In our example, if you want to add 8 to 3 first and use the result to multiply it by 5, you would write (8 + 3) * 5. This would produce 55. On the other hand, if you want to multiply 3 by 5 first then add the result to 8, you would write 8 + (3 * 5). This would produce 23. As you can see, results are different when parentheses are used on an operation that involves various operators. This concept is based on a theory called operator precedence. This theory manages which operation would execute before which one; but parentheses allow you to completely control the sequence of these operations. 
Practical Learning: Using the Parentheses in an Operation 
Private Sub cmdRCalculate_Click() Dim dblLength As Double Dim dblHeight As Double Dim dblPerimeter As Double Dim dblArea As Double dblLength = txtRLength dblHeight = txtRHeight ' Calculate the perimeter of the rectangle ' by adding the length to the height, 2 times each dblPerimeter = 2 * (dblLength + dblHeight) dblArea = dblLength * dblHeight txtRPerimeter = dblPerimeter txtRArea = dblArea End Sub 
Private Sub cmdECalculate_Click() Dim dblRadius1 As Double Dim dblRadius2 As Double Dim dblCircumference As Double Dim dblArea As Double dblRadius1 = txtEllipseRadius1 dblRadius2 = txtEllipseRadius2 dblCircumference = (dblRadius1 + dblRadius2) * 3.14159 dblArea = dblRadius1 * dblRadius2 * 3.14159 txtEllipseCircumference = dblCircumference txtEllipseArea = dblArea End Sub 
The Square Brackets Operator: [] 
In Lesson 2, we saw that it was suitable to use oneword names for objects in Microsoft Access. In reality, Microsoft Access, as mentioned already, is particularly flexible with names. We saw that we could use square brackets to enclose a name made of. As seen in Lesson 2, this principle is the same here. 
Practical Learning: Using the Square Brackets Operator 
Private Sub cmdRCalculate_Click() Dim dblLength As Double Dim dblHeight As Double Dim dblPerimeter As Double Dim dblArea As Double dblLength = [txtRLength] dblHeight = [txtRHeight] ' Calculate the perimeter of the rectangle ' by adding the length to the height, 2 times each dblPerimeter = 2 * (dblLength + dblHeight) dblArea = dblLength * dblHeight [txtRPerimeter] = dblPerimeter [txtRArea] = dblArea End Sub Private Sub cmdECalculate_Click() Dim dblRadius1 As Double Dim dblRadius2 As Double Dim dblCircumference As Double Dim dblArea As Double dblRadius1 = [txtEllipseRadius1] dblRadius2 = [txtEllipseRadius2] dblCircumference = (dblRadius1 + dblRadius2) * 3.14159 dblArea = dblRadius1 * dblRadius2 * 3.14159 [txtEllipseCircumference] = dblCircumference [txtEllipseArea] = dblArea End Sub 
The Collection Operator: ! 
Once again, in Lesson 2, we mentioned that the exclamation point operator "!" was used to access a member of a collection. 
Practical Learning: Using the Exclamation Operator 
Private Sub cmdSqCalculate_Click() Dim dblSide As Double Dim dblPerimeter As Double Dim dblArea As Double dblSide = Forms!frmAlgebraicOperators!txtSqSide ' Calculate the perimeter of a square by adding the side 4 times dblPerimeter = 4 * dblSide dblArea = dblSide ^ 2 Forms!frmAlgebraicOperators!txtSqPerimeter = dblPerimeter Forms!frmAlgebraicOperators!txtSqArea = dblArea End Sub 
The Line Continuation Operator: _ 
As introduced in Lesson 2, the line continuation character is used to span a section of code to more than one line. 
Practical Learning: Using the Underscore Operator 
Private Sub cmdCubeCalculate_Click() Forms!frmAlgebraicOperators!txtCubeArea = 6 * Forms!frmAlgebraicOperators!txtCubeSide _ * Forms!frmAlgebraicOperators!txtCubeSide Forms!frmAlgebraicOperators!txtCubeVolume = Forms!frmAlgebraicOperators!txtCubeSide * _ Forms!frmAlgebraicOperators!txtCubeSide * _ Forms!frmAlgebraicOperators!txtCubeSide End Sub 
Private Sub cmdBoxCalculate_Click() ' Volume = Length * Width * Height Forms!frmAlgebraicOperators!txtBoxVolume = _ Forms!frmAlgebraicOperators!txtBoxLength * _ Forms!frmAlgebraicOperators!txtBoxWidth * _ Forms!frmAlgebraicOperators!txtBoxHeight Dim dblLength, dblHeight, dblWidth As Double dblLength = Forms!frmAlgebraicOperators!txtBoxLength dblHeight = Forms!frmAlgebraicOperators!txtBoxWidth dblWidth = Forms!frmAlgebraicOperators!txtBoxHeight ' Area = 2 * ((L * H) + (H * W) + (L * W)) Forms!frmAlgebraicOperators!txtBoxArea = 2 * ( _ (dblLength * dblHeight) + _ (dblHeight * dblWidth) + _ (dblLength * dblWidth) _ ) End Sub 
Database Maintenance 
Database Creation 
So far, we have seen various ways of creating a database, including creating a blank database or using the wizard. Besides these techniques, you can also programmatically create a database. To do this, first declare a variable of type Application and initialize the variable with the version of the Microsoft Access that will be used. To actually create the database, call the NewCurrentDatabase method of the Application class. This method takes as argument the path and the name of the new database. The name should include the .mdb extension but if you omit it, the extension would be added when the database is created. Here is an example that creates a new database named Championship in a folder named Programs on the C: drive: Private Sub cmdCreateDatabase_Click() Dim strNewDB As String Dim appAccess As Access.Application strNewDB = "C:\Programs\Championship.mdb" Set appAccess = CreateObject("Access.Application.9") appAccess.NewCurrentDatabase strNewDB End Sub



Previous  Copyright © 20052012, FunctionX, Inc.  Next 
