asked on

round off Exponential values to decimal values

i'm using CR 8.5 with Access as the DB. My problem is to convert the exponential value in databse {PatReport.Result} as 9.49056888569691E-04 to decimal number restricted by , say n decimal places in my formula field

i tried doing it this way
formula = totext(CDbl({PatReport.Result}), "#0.00") ''for 2 decimal places

but this does not work, funny thing is it does not work for decimal values also.

also i tried formula = CStr(Round(CDbl({PatReport.Result}),NoOfDec))''NoOfDec is number with actual dec places required.

still does not work

Please Help urgently

Thank you,
Regards,
KJ

frodoman

>>> formula = totext(CDbl({PatReport.Result}), "#0.00") ''for 2 decimal places

When you say that this doesn't work, what do you mean? What are you expecting to see? This formula should show you 0.00 for the value you gave in your example.

Remember that this value is roughly .000949 so rounding to 2 decimal places *is* 0.00. Try rounding to 3 decimal places and you should see 0.001.

kapiljoshi

ASKER

No even if i increase it to 3 it compiles properly ,
but at run time it gives me the error
"String is Non-Numeric"

since it cannot convert Exponential number to Decimal (i.e. Double) i.e. i guess the problem is in CDBl

Regards,
KJ

kapiljoshi

ASKER

i forgot to mention {PatReport.Result} DB Field is of Type Text

frodoman

I don't think Crystal can convert this from a text value to a numeric value - because Crystal doesn't understand the exponential notation it doesn't consider xxxExx to be a numeric value.

My advice would be to alter this on the database side so you're selecting .0009xx instead of 9.xxxExx. If that isn't possible, I can probably write you a formula that will do the conversion but it'll be less efficient than bringing it into Crystal in decimal form.

frodoman

If you do want it, here's a formula that will do the conversion for you:

stringVar Original := '9.49056888569691E-04'; <-- Replace this with your db field
numberVar Base := cdbl(left(Original, instr(Original, 'E')-1));
numberVar Exponent := cdbl(right(Original, len(Original) - instrrev(Original, 'E')));
Base * (10 ^ Exponent );

This returns a numeric value then you can use Crystal's normal Format Field options to determine the decimal places and rounding.

kapiljoshi

ASKER

frodoman,

sorry for the delay, ur solution does work but as was expected it performs very poorly when there is lots of data in my report. so i try to insert only decimal format values from my front end in the DB. but again the funny part is the original ways dont work with normal decimal formats !!!

i.e.
formula = totext(CDbl({PatReport.Result}), "#0.00") ''for 2 decimal places &
formula = CStr(Round(CDbl({PatReport.Result}),NoOfDec))

Can u guess why?
I'm using Basic syntax. Also its not posible for me to change the DB Type from String to numeric at this moment.

Regards,
KJ

frodoman

Are all of the values decimal format - no exponential format? The problem you may be having is that Crystal is going to apply the formula to all of the records so you need to consider whether all of them are the same format.

frodoman

kapiljoshi

ASKER

No i havent used ur formula for Exponential to decimal; u can asume that all the values in the DB are in decimal fromat only

ASKER CERTIFIED SOLUTION

frodoman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial