Microsoft Office Tutorials and References
In Depth Information
Going beyond Basic Arithmetic
Rather than list all the functions that allow you to do complex math, we
provide a few examples in Table 2-2 to give you a sense of how these
functions work.
Table 2-2 Examples of Built-In Math and Financial Functions
Function and Syntax
Returns
Example
Absolute value (nega-
tive numbers convert to
positive numbers)
Abs(number)
Abs(-1) returns 1 .
Integer portion of a
number
Int(number)
Int(99.9) returns 99 .
Round(number
[,decimals])
Numerical value number
rounded to a
specified number of decimal
places (decimals)
Round(1.56789,2)
returns 1.57 .
Pmt(rate,
nper, po[,
fv[, type]])
Monthly payment on a
loan or annuity
Pmt(.058/12,
30*12, -50000)
returns 293.3765 (pay-
ment on a \$50,000 30-year
loan at 5.8 percent).
Book III
Chapter 2
If you need help with any function in Expression Builder, you can find all
the gory details that you need to make the function work for you in the
help system.
Formatting calculated numbers in queries
When you create a table and define a field as the Number data type, you can
choose a format, such as Currency, for displaying that number. In a query,
you don’t predefine a field’s data type. The number that appears as the
result of a calculation is often displayed as a General number — no dollar
sign and no fixed number of decimal places.
Figure 2-5 shows a query based on a hypothetical table named Loan
Scenarios. Within the Loan Scenarios table, the APR (annual
percentage rate) is a Number field with its Format property set to Percent.
The LoanAmount field is a Number field with its Format property set to
Currency. Those formats carry over to the results of the query (the query’s
Datasheet view). The result of the calculated MonthlyPayment field
displays as a General number with no currency sign, no commas, and a lot of
numbers to the right of the decimal point, as you see in the bottom half of
Figure 2-5.                     Search JabSto ::

Custom Search