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
Table 2-2 Examples of Built-In Math and Financial Functions
Function and Syntax
Absolute value (nega-
tive numbers convert to
Abs(-1) returns 1 .
Integer portion of a
Int(99.9) returns 99 .
Numerical value number
rounded to a
specified number of decimal
returns 1.57 .
Monthly payment on a
loan or annuity
returns 293.3765 (pay-
ment on a $50,000 30-year
loan at 5.8 percent).
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
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