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


kapiljoshiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

frodomanCommented:
>>> 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.
0
kapiljoshiAuthor Commented:
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
0
kapiljoshiAuthor Commented:
i forgot to mention {PatReport.Result} DB Field is of Type Text
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

frodomanCommented:
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.
0
frodomanCommented:
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.
0
kapiljoshiAuthor Commented:
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
0
frodomanCommented:
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
0
kapiljoshiAuthor Commented:
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
0
frodomanCommented:
Then you probably have either an invalid value in one or more of the fields (either a null or an empty string) and/or you have a decimal value that has a precision larger than Crystal can handle.  I don't remember exactly what the precision is for Crystal for if you have too many decimal places that can generate an overflow (just like having too large of an integer number can do the same thing).

frodoman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.