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.