Mixing Number and Currency Values

Wednesday 19 December 2012 @ 10:55 am

A user was getting an error in a Crystal formula and asked me to take a look. The formula looked like this:

{FieldA} / {FieldB} * 100

She was dividing one column by another to get a percentage and Crystal responded with “a number is required here” – pointing {FieldB}. I checked the data types of the fields, and found that {FieldA} was a numeric but that {FieldB} was a currency field. Apparently you can’t divide a numeric by a Currency. That was news to me so I did some testing and learned a few things:

1) You can divide a currency value by a numeric value or by another currency value with no error.
2) You can use % instead of / and the data types don’t matter
3) You can convert a currency value to a numeric value by using the function ToNumber() but NOT by using the function Val().

So there are two options that will work to accomplish our requirement:

{FieldA} / ToNumber ({FieldB}) * 100

or the much simpler:

{FieldA} % {FieldB}

In this last option the percent sign automatically moves the decimal two places so there is no need to multiply the result by 100.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro

Crystal Reports Server