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.