Microsoft Access Database Development With VBA

Operators and Operands

 

Declarative Operators

 

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 Indentation

Microsoft Visual Basic makes it very easy to write code by providing you skeleton code as we saw above, a very intuitive Code Editor, and other accessories. Indentation is a technique that allows you to write easily readable code. It consists of visually showing the beginning and end of a section of code. Indentation is done at various levels. For the code of an event, indentation consists of moving your code to the right side so that the only line to the extreme left are those of the Private and End Sub lines, unless specified otherwise.

The easiest and most common way to apply indentation consists of pressing Tab before typing your code. By default, one indentation, done when pressing Tab, corresponds to 4 characters. This is controlled by the Editor property page of the Options dialog box. To change it, on the main menu of Microsoft Visual Basic, you would click Tools -> Options and use the Tab Width text box of the Editor property page:

If you don't want the pressing of Tab to be equivalent to 4 characters, change the value of the Tab Width text box to a reasonable value and click OK. Otherwise, it is (strongly) suggested that you keep it to its default of 4 characters.

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

Practical LearningPractical Learning: Using the Assignment Operator

  1. Start Microsoft Access
  2. From the resources that accompany these lessons, open the Exercise2 database
  3. In the Navigation Pane, right-click the Assignment form and click Design View
  4. On the form, double-click the First Name text box to select it
  5. In the Properties window, click the Event tab
  6. Double-click On Lost Focus
  7. Right-click On Lost Focus and click Build...
  8. Implement the event as follows:
    Private Sub txtFirstName_LostFocus()
        txtFullName = txtFirstName
    End Sub
  9. Return to the form and switch it to Form View
  10. Click the First Name text box. Type Catherine and press Tab
     
    Using the Assignment Operator
  11. Notice that the Full Name text box got filled with the value of the First Name when the First Name text box lost focus.
    After using the form, switch it to Design View

The Comma ,

The comma is used to separate variables used in a group. For example, a comma can be used to delimit the names of variables that are declared on the same line. Here is an example:

Sub Exercise()
    Dim FirstName As String, LastName As String, FullName As String
End Sub

The Colon Operator :

Most of the time, to make various statements easier to read, you write each on its own line. Here are examples:

Sub Exercise()
    Dim FirstName As String, LastName As String
    
    FirstName = "Arsène"
    LastName = "Nkoulou"
End Sub

The Visual Basic language allows you to write as many statements as necessary on the same line. When doing this, the statements must be separated by a colon. Here is an example:

Sub Exercise()
    Dim FirstName As String, LastName As String
    
    FirstName = "Arsène" : LastName = "Nkoulou"
End Sub

String Operators

 

The Double Quotes: ""

Double-quotes 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

Practical Learning: Using the Double-Quote Operator

  1. On the Assignment form, right-click the Full Name text box and click Build Event...
  2. On the Choose Builder dialog box, click Code Builder
     
    Code Builder
  3. Click OK
  4. Once in the Code Editor, in the Object combo box, select cmdReset
  5. Implement its Click event as follows:
    Private Sub cmdReset_Click()
        txtFirstName = ""
        txtMI = ""
        txtLastName = ""
        txtFullName = ""
        txtUsername = ""
    End Sub
  6. Click the empty line under Private Sub txtFullName_Click. Notice that the Object combo box displays txtFullName
  7. In the Procedure combo box, select DblClick
  8. Implement the event as follows:
    Private Sub txtFullName_DblClick(Cancel As Integer)
        Dim strFullName As String
        strFullName = "Mary D. Lunden"
        txtFullName = strFullName
    End Sub
  9. Return to the form and switch it to Form View
  10. Double-click the Full Name text box
  11. After viewing the form, switch it to Design View and return to Microsoft Visual Basic

The String Concatenator: &

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

Author Note =FirstName & " " & LastName
This would display, for example, Boniface Dunkirk
  =[LastName] & ", " & [FirstName]
This would produce, for example, Chang, Helene
  =[Address] & " " & [City] & " " & [State] & " " & [ZIPCode] & " " & [Country]
This would display a complete address in a field
 

Practical Learning: Using the Concatenator

  1. In the Code Editor, delete the code in the txtFirstName_LostFocus event
  2. In the Object combo box, select cmdCreateAccount
  3. Implement its 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 = txtFirstName
        strMiddleInitial = txtMI
        strLastName = txtLastName
        strUsername = txtLastName & strMiddleInitial
        strFullName = txtFirstName & " " & txtLastName
        
        txtFullName = strFullName
        txtUsername = strUsername
    End Sub
  4. Return to Microsoft Access and switch the form to Form View
  5. Click First Name
  6. Type Hermine and press Tab
  7. Type D and press Tab
  8. In the Last Name text box, type Summers and press Tab
  9. Notice that the first button is selected. Press Enter
  10. Close the dialog box. When asked whether you want to save the form, click Yes

Arithmetic Operators

 

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

  1. In the Navigation pane, double the AlgebraicOperators form to open it
     
    Algebraic Operators
  2. After viewing it, switch it to Design View
  3. The accompanying resources include pictures of geometric figures. To enhance the form, you can add them. To do that, on the Ribbon, click Image and click the left area of the labels. On the Insert Picture dialog box, locate the picture and add it.
  4. On the form, click the Quadrilateral tab. Right-click the top Calculate button and click Build Event...
  5. On the Choose Builder dialog box, double-click Code Builder
  6. In the Object combo box, select cmdRCalculate
  7. Implement both Click events as follows:
    Private Sub cmdSqCalculate_Click()
        Dim dblSide As Double
        Dim dblPerimeter As Double
        
        dblSide = txtSqSide
        dblPerimeter = dblSide + dblSide + dblSide + dblSide
        txtSqPerimeter = dblPerimeter
    End Sub
    
    Private Sub cmdRCalculate_Click()
        Dim dblLength, dblHeight As Double
        Dim dblPerimeter As Double
        
        dblLength = txtRLength
        dblHeight = txtRHeight
        dblPerimeter = dblLength + dblHeight + dblLength + dblHeight
        txtRPerimeter = dblPerimeter
    End Sub
  8. Return to the form and switch it to Form View
  9. In the Quadrilateral tab, click Side and type 35.55
  10. Click the top Calculate button
  11. Click Length and type 42.72
  12. Click Height and type 36.44
  13. Click the other Calculate button
  14. After using the form, return to the Code Editor

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

  1. To apply the multiplication operation, change the Click events as follows:
    Private Sub cmdSqCalculate_Click()
        Dim dblSide As Double
        Dim dblPerimeter As Double
        Dim dblArea As Double
        
        dblSide = txtSqSide
        dblPerimeter = 4 * dblSide
        dblArea = dblSide * dblSide
        
        txtSqPerimeter = dblPerimeter
        txtSqArea = dblArea
    End Sub
    
    Private Sub cmdRCalculate_Click()
        Dim dblLength, dblHeight As Double
        Dim dblPerimeter As Double
        Dim dblArea As Double
        
        dblLength = txtRLength
        dblHeight = txtRHeight
        dblPerimeter = dblLength + dblHeight + dblLength + dblHeight
        dblArea = dblLength * dblHeight
        
        txtRPerimeter = dblPerimeter
        txtRArea = dblArea
    End Sub
  2. Get back to the form and click both Calculate buttons:
     
    Calculations
  3. After using the form, switch it to Design View
  4. Get back to the Code Editor
  5. In the Object combo box, select cmdCCalculate and implement its Click event as follows:
    Private Sub cmdCCalculate_Click()
        Dim dblRadius As Double
        Dim dblCircumference, dblArea As Double
        
        dblRadius = txtCircleRadius
        dblCircumference = 2 * dblRadius * 3.14159
        dblArea = 3.14159 * dblRadius * dblRadius
        
        txtCircleCircumference = dblCircumference
        txtCircleArea = dblArea
    End Sub
  6. Return to the form and switch it to Form View
  7. Click the Circular tab and change the top Radius (the radius of the circle) to 64.88
  8. Click the top Calculate button:
     
    Calculations
  9. After using the form, switch it to Design View

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:

yx

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

  1. Return to the Code Editor and change the code of the cmdSqCalculate_Click as follows:
    Private Sub cmdSqCalculate_Click()
        Dim dblSide As Double
        Dim dblPerimeter As Double
        Dim dblArea As Double
        
        dblSide = txtSqSide
        dblPerimeter = 4 * dblSide
        dblArea = dblSide ^ 2
        
        txtSqPerimeter = dblPerimeter
        txtSqArea = dblArea
    End Sub
  2. Get to the form and switch it to Form View
  3. In the Quadrilateral property sheet, enter 12.46 in the Side text box
  4. Click the top Calculate
  5. Close the form
  6. When asked whether you want to save, click Yes

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

 

 
 
 

Microsoft Access and Visual Basic Operators

 

Comments


 

A comment is a piece of text in a code section that the database engine would not consider when reading your code. As such, a comment can be written any way you want.

In Visual Basic, the line that contains a comment can start with a single quote. Here is an example:

Private Sub Form_Load()
    ' This line will not be considered as part of the code
End Sub

Alternatively, you can start a comment with the Rem keyword. Anything on the right side of rem, Rem, or REM would not be read. Here is an example:

Private Sub Form_Load()
    ' This line will not be considered as part of the code
    Rem I can write anything I want on this line
End Sub

Comments are very useful and it is strongly suggested that you use them regularly. They can never hurt your code and they don't increase the size of your database. Comments can help you and other people who read your code to figure out what a particular section of code is used for, which can be helpful when you re-visit your code after months or years of not seeing it.

Practical Learning: Using the Concatenator

  1. In the Navigation Pane, right-click Assignments and click Design View
  2. Right-click the button in the top-right section of the form and click Build Event
  3. Change the Click event of the cmdCreateAccount 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 = txtFirstName
        strMiddleInitial = txtMI
        strLastName = 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 & " " & txtLastName
        
        txtFullName = strFullName
        txtUsername = strUsername
    End Sub
  4. Return to Microsoft Access, switch the form to Form View, and click First Name
  5. Type Hermine and press Tab
  6. Type D and press Tab
  7. In the Last Name text box, type Summers and press Tab
  8. Notice that the first button is selected. Press Enter
  9. Close the dialog box
  10. When asked whether you want to save the form, click Yes
  11. In the Navigation Pane, right-click the AlgebraicOperators form and click Design View
  12. Right-click the top Calculate button and click Build Event...
  13. In the Code Editor, change the codes of the following events:
    Private Sub cmdCCalculate_Click()
        Dim dblRadius As Double
        Dim dblCircumference, dblArea As Double
        
        dblRadius = txtCircleRadius
        ' Circumference of a circle = 2 * Radius * PI
        dblCircumference = 2 * dblRadius * 3.14159
        ' Area of a circle = Radius * Radius * PI
        dblArea = 3.14159 * dblRadius * dblRadius
    
        txtCircleCircumference = dblCircumference
        txtCircleArea = dblArea
    End Sub
    
    Private Sub cmdRCalculate_Click()
        Dim dblLength, 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 = dblLength + dblHeight + dblLength + dblHeight
        dblArea = dblLength * dblHeight
    
        txtRPerimeter = dblPerimeter
        txtRArea = dblArea
    End Sub
    
    Private Sub cmdSqCalculate_Click()
        Dim dblSide As Double
        Dim dblPerimeter As Double
        Dim dblArea As Double
        
        dblSide = txtSqSide
        ' Calculate the perimeter of a square by adding the side 4 times
        dblPerimeter = 4 * dblSide
        dblArea = dblSide * 2
        
        txtSqPerimeter = dblPerimeter
        txtSqArea = dblArea
    End Sub
    

Set

We saw earlier that you could declare a variable based on a built-in 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

Carriage Return-Line Feed

If you are displaying a string but judge it too long, you can segment it in appropriate sections as you see fit. To do this, you can use vbCrLf. Here is an example:

Sub Exercise()
    Dim FirstName As String, LastName As String, FullName As String
    Dim Accouncement As String
    
    FirstName = "Valère"
    LastName = "Edou"
    FullName = FirstName & " " & LastName
    Accouncement = "Student Registration - Student Full Name: " & _
                   vbCrLf & FullName
End Sub

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 multi-operator 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

  1. In the Code Editor, change the following event:
    Private Sub cmdRCalculate_Click()
        Dim dblLength As Double, 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
  2. In the Object combo box, select cmdECalculate and implement its event as follows:
    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
  3. Return to the form
  4. Click Circular
  5. Click the top Radius text box and type 16.48
  6. Click the top Calculate button
  7. Click radius and type 20.24
  8. Click the bottom Radius text box and type 63.15
  9. Click the second Calculate button
     
    Calculations
  10. Return to the Code Editor in Microsoft Visual Basic

The Square Brackets Operator: []

We know that it is suitable to use one-word 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

  • Change the following events:
    Private Sub cmdRCalculate_Click()
        Dim dblLength As Double, 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: !

We know that the exclamation point operator "!" is used to access a member of a collection. 

Practical Learning: Using the Exclamation Operator

  • Change the code of the cmdSqCalculate_Click event as follows:
    Private Sub cmdSqCalculate_Click()
        Dim dblSide As Double
        Dim dblPerimeter As Double
        Dim dblArea As Double
        
        dblSide = Forms!AlgebraicOperators!txtSqSide
        ' Calculate the perimeter of a square by adding the side 4 times
        dblPerimeter = 4 * dblSide
        dblArea = dblSide ^ 2
        
        Forms!AlgebraicOperators!txtSqPerimeter = dblPerimeter
        Forms!AlgebraicOperators!txtSqArea = dblArea
    End Sub

The Line Continuation Operator: _

It is usually suitable to write lines of code that are not too long. This makes it easy to read code and avoid scrolling left and right. In some cases, you will not have much choice but to create long expressions. Unlike many other languages such as C/C++/C#, Java, etc, Visual Basic doesn't allow to simply continue a line of code from one line to the next without alerting the compiler. Still, if a line of code becomes too long, there is a technique you can use to span on various lines.

To continue a piece of code from one line to the next, type an empty space followed by an underscore symbol, then continue your code on the next line.

Practical Learning: Using the Underscore Operator

  1. In the Code Editor, click the arrow of the Object combo box and select cmdCubeCalculate_Click
  2. Implement the Click event as follows:
    Private Sub cmdCubeCalculate_Click()
        Forms!AlgebraicOperators!txtCubeArea = 6 * _
    				Forms!AlgebraicOperators!txtCubeSide _
                                  * Forms!AlgebraicOperators!txtCubeSide
        Forms!AlgebraicOperators!txtCubeVolume = _
    				Forms!AlgebraicOperators!txtCubeSide * _
                                    Forms!AlgebraicOperators!txtCubeSide * _
                                    Forms!AlgebraicOperators!txtCubeSide
    End Sub
  3. In the Object combo box, select cmdBoxCalculate and implement its Click event as follows:
    Private Sub cmdBoxCalculate_Click()
        ' Volume = Length * Width * Height
        Forms!AlgebraicOperators!txtBoxVolume = _
                Forms!AlgebraicOperators!txtBoxLength * _
                Forms!AlgebraicOperators!txtBoxWidth * _
                Forms!AlgebraicOperators!txtBoxHeight
                
        Dim dblLength As Double, _
        	dblHeight As Double, _
        	dblWidth As Double
        dblLength = Forms!AlgebraicOperators!txtBoxLength
        dblHeight = Forms!AlgebraicOperators!txtBoxWidth
        dblWidth  = Forms!AlgebraicOperators!txtBoxHeight
        ' Area = 2 * ((L * H) + (H * W) + (L * W))
        Forms!AlgebraicOperators!txtBoxArea = 2 * ( _
                                                     (dblLength * dblHeight) + _
                                                     (dblHeight * dblWidth) + _
                                                     (dblLength * dblWidth) _
                                                  )
    End Sub
  4. Return to the form and switch it to Form View
  5. Click the 3-Dimensions tab
  6. Click the Side text box and type 16.48
  7. Click the top Calculate button
  8. Click Length and type 20.24
  9. Click Width and type 63.15
  10. Click Height and type 42.24
  11. Click the second Calculate button
     
    Calculations
  12. Save the form and close it

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

Bit Manipulations

 

Introduction

From our introduction to variables, you may remember that the computer stores its data in memory using small locations that look like boxes and each box contains a bit of information. Because a bit can be represented only either as 1 or 0, we can say that each box contains 1 or 0. Bit manipulation consists of changing the value (1 or 0, or 0 or 1) in a box. As we will see in the next few operations, it is not just about changing a value. It can involve reversing a value or kind of "moving" a box from its current position to the next position.

The operations on bits are performed on 1s and 0s only. This means that any number in decimal or hexadecimal format involved in a bit operation must be converted to binary first.

You will almost never perform some of the operations we are going to review. You will hardly perform some other operations. There is only one operation you will perform sometimes: the OR operation.

"Reversing" a Bit

Remember that, at any time, a box (or chunk) in memory contains either 1 or 0:

Bit 0 Bit 1
0 1

Bit reversal consists of reversing the value of a bit. If the box contains 1, you can reverse it to 0. If it contains 0, you can reverse it to 1. To support this operation, the Visual Basic language provides the Not Operator.

As an example, consider the number 286. The decimal number 286 converted to binary is 100011110. You can reverse each bit as follows:

286 1 0 0 0 1 1 1 1 0
Not 286 0 1 1 1 0 0 0 0 1
 

Bitwise Conjunction

Bitwise conjunction consists of adding the content of one box (a bit) to the content of another box (a bit). To support the bitwise conjunction operation, the Visual Basic language provides the And operator.

To perform the bit addition on two numbers, remember that they must be converted to binary first. Then:

  • If a bit with value 0 is added to a bit with value 0, the result is 0
     
    Bit0 0
    Bit1 0
    Bit0 And Bit1 0
  • If a bit with value 1 is added to a bit with value 0, the result is 0
     
    Bit0 1
    Bit1 0
    Bit0 And Bit1 0
  • If a bit with value 0 is added to a bit with value 1, the result is 0
     
    Bit0 0
    Bit1 1
    Bit0 And Bit1 0
  • If a bit with value 1 is added to a bit with value 1, the result is 1
     
    Bit0 1
    Bit1 1
    Bit0 And Bit1 1

As an example, consider the number 286 bit-added to 475. The decimal number 286 converted to binary is 100011110. The decimal number 4075 converted to binary is 111111101011. Based on the above 4 points, we can add these two numbers as follows:

286 0 0 0 1 0 0 0 1 1 1 1 0
4075 1 1 1 1 1 1 1 0 1 0 1 1
286 And 4075 0 0 0 1 0 0 0 0 1 0 1 0

Therefore, 286 And 4075 produces 100001010 which is equivalent to:

  Bit8 Bit7 Bit6 Bit5 Bit4 Bit3 Bit2 Bit1 Bit0
  256 128 64 32 16 8 4 2 1
286 And 4075 1 0 0 0 0 1 0 1 0
  256 0 0 0 0 8 0 2 0

This means that 286 And 4075 = 256 + 16 + 2 = 266

This can also be programmatically calculated as follows:

Sub Exercise()
    Dim Number1 As Integer
    Dim Number2 As Integer
    Dim Result As Integer
        
    Number1 = 286
    Number2 = 4075
    Result = Number1 And Number2
End Sub

Bitwise Disjunction

Bitwise disjunction consists of disjoining one a bit from another bit. To support this operation, the Visual Basic language provides the Or operator.

To perform a bitwise conjunction on two numbers, remember that they must be converted to binary first. Then:

  • If a bit with value 0 is added to a bit with value 0, the result is 0
     
    Bit0 0
    Bit1 0
    Bit0 Or Bit1 0
  • If a bit with value 1 is added to a bit with value 0, the result is 1
     
    Bit0 1
    Bit1 0
    Bit0 Or Bit1 1
  • If a bit with value 0 is added to a bit with value 1, the result is 1
     
    Bit0 0
    Bit1 1
    Bit0 Or Bit1 1
  • If a bit with value 1 is added to a bit with value 1, the result is 1
     
    Bit0 1
    Bit1 1
    Bit0 Or Bit1 1

As an example, consider the number 305 bit-disjoined to 2853. The decimal number 305 converted to binary is 100110001. The decimal number 2853 converted to binary is 101100100101. Based on the above 4 points, we can disjoin these two numbers as follows:

305 0 0 0 1 0 0 1 1 0 0 0 1
2853 1 0 1 1 0 0 1 0 0 1 0 1
305 Or 2853 1 0 1 1 0 0 1 1 0 1 0 1

Therefore, 305 Or 2853 produces 101100110101 which is equivalent to:

  Bit11 Bit10 Bit9 Bit8 Bit7 Bit6 Bit5 Bit4 Bit3 Bit2 Bit1 Bit0
  2048 1024 512 256 128 64 32 16 8 4 2 1
305 Or 2853 1 0 1 1 0 0 1 1 0 1 0 1
  2048 0 512 256 0 0 32 16 0 4 0 1

This means that 286 And 4075 = 2048 + 512 + 256 + 32 + 16 + 4 + 1 = 2869

This can also be programmatically calculated as follows:

Sub Exercise()
    Dim Number1 As Integer
    Dim Number2 As Integer
    Dim Result As Integer
        
       
    Number1 = 286
    Number2 = 4075
    Result = Number1 Or Number2
End Sub

Bitwise Exclusion

Bitwise exclusion consists of adding two bits with the following rules. To support bitwise exclusion, the Visual Basic language provides an operator named Xor:

  • If both bits have the same value, the result is 0
     
    Bit0 0 1
    Bit1 0 1
    Bit0 Xor Bit1 0 0
  • If both bits are different, the result is 1
     
    Bit0 0 1
    Bit1 1 0
    Bit0 Xor Bit1 1 1

As an example, consider the number 618 bit-excluded from 2548. The decimal number 618 converted to binary is 1001101010. The decimal number 2548 converted to binary is 100111110100. Based on the above 2 points, we can bit-exclude these two numbers as follows:

618 0 0 1 0 0 1 1 0 1 0 1 0
2548 1 0 0 1 1 1 1 1 0 1 0 0
618 Xor 2548 1 0 1 1 1 0 0 1 1 1 1 0

Therefore, 305 Or 2853 produces 101110011110 which is equivalent to:

  Bit11 Bit10 Bit9 Bit8 Bit7 Bit6 Bit5 Bit4 Bit3 Bit2 Bit1 Bit0
  2048 1024 512 256 128 64 32 16 8 4 2 1
618 Xor 2548 1 0 1 1 1 0 0 1 1 1 1 0
  2048 0 512 256 128 0 0 16 8 4 2 0

This means that 286 And 4075 = 2048 + 512 + 256 + 128 + 16 + 8 + 4 + 2 = 2974

This can also be programmatically calculated as follows:

Sub Exercise()
    Dim Number1 As Integer
    Dim Number2 As Integer
    Dim Result As Integer
        
       
    Number1 = 286
    Number2 = 4075
    Result = Number1 Xor Number2
End Sub

Practical LearningPractical Learning: Ending the Lesson

  • To close Microsoft Access, click FILE -> Close
 
 
   
 

Previous Copyright © 2002-2013 FunctionX, Inc. Next