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).