Microsoft Office Tutorials and References
In Depth Information
Going beyond Basic Arithmetic
Main Built-In Conversion Functions
Acceptable Expression Type
String or number
Number from 0 to 255
Whole number from –32,768 to
The big trick is to enclose the entire expression (everything to the right of
the field name and colon) within the conversion function’s parentheses in
the QBE grid. To display the MonthlyPayment field from the sample Loan
Scenarios query as Currency data, for example, you must contain the entire
expression within the CCur() parentheses, as in the following expression:
MonthlyPayment: CCur( Pmt([APR]/12,[Years]*12,-[LoanAmount]) )
Figure 2-6 shows the result of using CCur() in the MonthlyPayment
calculated control to display the results of the expression in Currency format.
What’s with the 12s in the expression?
If you’re wondering why the sample expression
contains things like /12 and *12 , the answer
has to do with how the Pmt() function works.
The APR value is the annual percentage rate,
and the term of the loan is expressed in years.
When you want the Pmt() function to return
a monthly payment, you need to divide the
annual percentage rate by 12: [APR]/12 .
You also need to multiply the number of years
by 12 to get the number of monthly payments:
Typically, Pmt() returns a negative number
as the result, because each payment is a
debit (expense). By placing a minus sign in
front of the LoanAmount field name (that
is, –[LoanAmount] ), you convert that
LoanAmount to a negative number (a debit),
which in turn converts the calculated monthly
payment to a credit (a positive number).