Simple Math with Numeric Fields
Many calculated fields rely entirely on ordinary high school math. Table-1 gives a quick overview of your basic options for combining numbers.
Table-1. Arithmetic Operators| Operator | Name | Example | Result |
|---|---|---|---|
| + | Addition | 1+1 | 2 |
| - | Subtraction | 1-1 | 0 |
| * | Multiplication | 2*2 | 4 |
| ^ | Exponentiation | 2^3 | 8 |
| / | Division | 5/2 | 2.5 |
| \ | Integer division (returns the lowest whole number and discards the remainder) | 5\2 | 2 |
| Mod | Modulus (returns the remainder left after division) | 5 Mod 2 | 1 |
You're free to use as many fields and operators as you need to create your expression. Consider a Products table with a QuantityInStock field that records the number of units in your warehouse. To determine the value you have on hand for a given product, you can write this expression that uses two fields:
ValueInStock: [UnitsInStock] * [Price]
Tip: When performing a mathematical operation with a field, you'll run into trouble if the field contains a blank value. To correct the problem, you need the Nz( ) function.
Date fields
You can also use the addition and subtraction operators with date fields. (You can use multiplication, division, and everything else, but it doesn't have any realistic meaning.)
Using addition, you can add an ordinary number to a date field. This number moves the date forward by that many days. Here's an example that adds two weeks of headroom to a company deadline:
ExtendedDeadline: [DueDate] + 14
If you use this calculation with the date January 10, 2007, the new date becomes January 24, 2007.
Using subtraction, you can find the number of days between any two dates. Here's how you calculate how long it was between the time an order was placed and when it was shipped:
ShippingLag: [ShipDate] - [OrderDate]
If the ship date occurred 12 days after the order date, you'd see a value of 12.
Note: Date fields can include time information. In calculations, the time information's represented as the fractional part of the value. If you subtract two dates and wind up with the number 12.25, that represents 12 days and six hours (because six hours is 25 percent of a full day).
Remember, if you want to include literal dates in your queries (specific dates you supply), you need to bracket them with the # character and use Month/Day/Year format. Here's an example that uses that approach to count the number of days between the date students were expected to submit an assignment (March 20, 2007) and the date they actually did:
LateDays: [DateSubmitted] - #03/20/07#
A positive value indicates that the value in DateSubmitted is larger (more recent) than the deadline datein other words, the student was late. A value of 4 indicates a student that's four days off the mark, while 4 indicates a student that handed the work in four days ahead of schedule.
Order of operations
If you have a long string of calculations, Access follows the standard rules for order of operations: mathematician-speak for deciding which calculation to perform first when there's more than one calculation in an expression. So if you have a lengthy expression, Access doesn't just carry on from left to right. Instead, it evaluates the expression piece by piece in this order:
- Parentheses (Access always performs any calculations within parentheses first)
- Percent
- Exponents
- Division and multiplication
- Addition and subtraction
Suppose you want to take the QuantityInStock and the QuantityOnOrder fields into consideration to determine the value of all the product you have available and on the way. If you're not aware of the order of operation rules, then you might try this expression:
TotalValue: [UnitsInStock] + [UnitsOnOrder] * [Price]
The problem here is that Access multiplies QuantityOnOrder and Price together, and then adds it to the QuantityInStock. To correct this oversight, you need parentheses like so:
TotalValue: ([UnitsInStock] + [UnitsOnOrder]) * [Price]
Now the QuantityInStock and QuantityOnOrder fields are totaled together, and then multiplied with the Price to get a grand total.
Tip: Need some more space to write a really long expression? You can widen any column in the query designer to see more at once, but you'll still have trouble with complex calculations. Better to click in the Field box, and then press Shift+F2. This action pops open a dialog box named Zoom, which shows the full content in a large text box, wrapped over as many lines as necessary. When you've finished reviewing or editing your expression, click OK to close the Zoom box and keep any changes you've made, or Cancel to discard them.