[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

round off Exponential values to decimal values

Posted on 2006-04-20
11
Medium Priority
?
1,411 Views
Last Modified: 2008-01-09
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


0
Comment
Question by:kapiljoshi
  • 5
  • 4
9 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 16497648
>>> 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
 

Author Comment

by:kapiljoshi
ID: 16498196
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
 

Author Comment

by:kapiljoshi
ID: 16498223
i forgot to mention {PatReport.Result} DB Field is of Type Text
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
LVL 42

Expert Comment

by:frodoman
ID: 16498511
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
 
LVL 42

Expert Comment

by:frodoman
ID: 16498561
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
 

Author Comment

by:kapiljoshi
ID: 16532623
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
 
LVL 42

Expert Comment

by:frodoman
ID: 16533584
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
 

Author Comment

by:kapiljoshi
ID: 16533659
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
 
LVL 42

Accepted Solution

by:
frodoman earned 500 total points
ID: 16534496
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses
Course of the Month18 days, 11 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question