Microsoft Office Tutorials and References
In Depth Information
Going beyond Basic Arithmetic
Table 2-3
Main Built-In Conversion Functions
Function
Acceptable Expression Type
Return Type
String or number
Boolean
CBool(expression)
CByte(expression)
Number from 0 to 255
Byte
CCur(expression)
Number
Currency
CDate(expression)
Date/time
Date
CDbl(expression)
Number
Double
CDec(expression)
Number
Decimal
Whole number from –32,768 to
32,767
Integer
CInt(expression)
CLng(expression)
Whole number
Long
Number
Single
CSng(expression)
Any
String
CStr(expression)
Any
Variant
CVar(expression)
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:
Book III
Chapter 2
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:
[Years]*12 .
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).
Search JabSto ::




Custom Search