Join Curt Frye for an in-depth discussion in this video Creating a calculation using mathematical operators, part of Up and Running with VBA in Access.
- View Offline
When you analyze data in Access you will often use mathematical operators to create the expressions you need. In this movie, I will describe the operators available to you and show you how to use them. For this movie, we will use the Demo Operators code module, so I'll just go into the Navigation pane and double-click it. This code module, called DemonstrateOperators, has the varResult variable, which is defined as a variant, and then a message box at the end, which displays the value of the varResult variable. And I defined that as a variant because my calculations will produce a wide variety of results, and no other single variable type could cover all the possibilities.
Now the first operator here at the top is called the equal sign, and the equal sign can be used in two ways: as a test for equivalence or to assign a value to a variable. So let's work with variable assignment first. Let's say that I have varResult, space, then an equal sign, and then 14. Now when I press F5 Access will display a message box with the value of the varResult variable and pressing F5, we see the value of 14, and that makes sense because I just assigned that value to the variable.
But now let's say that I want to test equivalence, and the easiest way to do that is just type two different numbers. So I will backspace over the 14, type a left parenthesis. So now we are checking to see where the expression 14 = 9 and then a right parentheses is true. So in other words we are checking for equivalence on the right side, and we are using the equal sign to assign the result of that comparison. It will be either true or false, and that's normally the Boolean variable type, but a variant can handle any type of data, so we will use it.
Now, if I press F5 to run the code, we see that the result is false, and that is as expected; 14 does not equal 9. I will go ahead and comment out that line of code and move down to the plus, minus, multiply, and divide symbols, and these are almost certainly familiar to you. So let's say that I have varResult = 14 + 9. Now, when I press F5 I should get the result of 23, and I do. And just to run through, 14-9, F5, is 5.
14*9, which is the asterisk, and F5, is 126 and finally 14/9 is, pressing F5, 1.5 repeating. Now notice that the division, the regular division operator, is the forward slash, and that is the slash that starts to the left at the bottom and goes up and the right, and that will be important when we get to another operator later in this movie.
I will go ahead and comment out the line and click below the exponentiation. The caret, which you type by pressing Shift+6 on most standard keyboards, is used to apply an exponent to a number. So let's say that varResult = 2^3, which is 2 to the 3rd power, and press F5, and I got the result of 8. And comment that out. Now the next two operators aren't as commonly used, but they are actually very handy.
The first one is the backslash, and that is used for integer division. Integer division tells you how many times a number goes into another number, and discards the remainder. So let's say that you have an olive oil company and you sell bottles of olive oil in 12-bottle cases. If you have 30 bottles--and let's go ahead and type that in--varResult = 30 and then a backslash 12, you can find out how many 12 bottle cases you can make from 30 bottles.
So if I press F5 to run the code, we see that we get 2. The final operation is called modular division, and that is the complement of integer division. What it does is ask, what is the remainder? In other words, how much is left over as a result of this division operation? So I will go ahead and comment out this previous code and click below the Mod comment there. And I will assign varResult = 30 Mod 12.
So the result should be, how many bottles do I have leftover if I'm making 12 bottle cases out of 30 bottles? And when I press F5 I get 6, and that makes sense. 2 12-bottle cases is 24 bottles, out of 30, so I have 6 left over. The mathematical operators available in Access VBA let you calculate any values you need. You might not use integer division and modular division as often as you use the other operations, but they are handy tools to have at your disposal.
- Working with macros and managing macro security settings
- Creating, exporting, and deleting code modules
- Running a VBA procedure
- Debugging VBA code
- Defining variables, constants, and calculations
- Displaying a calculated result in a message box
- Repeating tasks with loops
- Creating conditional statements
- Manipulating database objects, such as forms, tables, and queries
- Adding, editing, and deleting records
- Looking up values in a table
- Requerying and repainting forms