Split / Divide decimal numbers
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=18177
Printed Date: 01 May 2024 at 10:19pm
Topic: Split / Divide decimal numbers
Posted By: Goldberg
Subject: Split / Divide decimal numbers
Date Posted: 04 Dec 2012 at 11:33pm
Hello,
I need to toword (decimal) numbers (and write text between the unit and the decimal part), but I'm having trouble because on the decimal part of the number it will text it as <decimalnumber not in text> / 100, so I'm trying to split them and then toword it
For example:
I have 53,14 and I want to split it into 53 and into 14.
I've tried truncate, but the decimal part splits as 0,14 and even if I multiply it by 100 it won't give me 14 (in this case it would give me 13,6).
So, my question is:
How can I do it?
Thanks
|
Replies:
Posted By: DBlank
Date Posted: 05 Dec 2012 at 3:35am
|
what do you want 53.14 to end up looking like?
|
Posted By: comatt1
Date Posted: 05 Dec 2012 at 4:22am
you can convert to a string before, and manipulate it. But then again, I have the same question, what is your goal, what do you eventually want this 53.14 to output as?
If you just want '53 14' just do a (replace(totext(field),',',' '))
|
Posted By: Goldberg
Date Posted: 05 Dec 2012 at 4:50am
53,14 is just an example and it's meant to be, let's say, the worker's wage.
So, I want to make it toword and turn 53,14 into fifty three euros and fourteen cents.
Thanks
|
Posted By: comatt1
Date Posted: 05 Dec 2012 at 5:05am
%20 - http://stackoverflow.com/questions/8432527/convert-number-to-words-first-second-third-and-so-on
this is not a simple task in my experience, but there are many resources I have used to accomplish this; this is one.
|
Posted By: Goldberg
Date Posted: 05 Dec 2012 at 5:58am
Where should I put that SQL code?
Crystal sintax won't support it.
Thanks
|
Posted By: comatt1
Date Posted: 05 Dec 2012 at 5:59am
hmmm... go to
stackoverflow.com and just search for converting numbers to words.
an idea may be to split the two numbers (which could get complicated explaining since you Euros have all the commas/periods switched).
should grab the base number, no thousands separator;
left(replace(totext(@Data),'.',''), PatIndex(',', replace(totext(@Data),'.','') + '1') - 1)
should grab after the decimal;
right(replace(totext(@Data),'.',''), len(@data) - (PatIndex(',', replace(totext(@Data),'.','') )-1) )
FYI I replaced your thousands separator just for ease.
if you convert back to number on both values you should be able to convert both 'int' TOWORDS() without getting the fraction.
I haven't tested.
There are bound to be times you will encounter errors, with this method, but it will be easier than coding a whole new command without experience
---
Answer, separate your values,
take value to left of ',' or decimal whatever you call it
take value to right of ',' and then convert
may have to use left/right/instr/mid functions though.
if I typed it here would just be copying and pasting from another post though, so search on here for separating a string.
|
Posted By: Goldberg
Date Posted: 05 Dec 2012 at 6:04am
|
Yes, sorry I edited it because I then searched on google and found it
|
Posted By: Goldberg
Date Posted: 05 Dec 2012 at 10:56pm
Using
left(replace(totext(@Data),'.',''), PatIndex(',', replace(totext(@Data),'.','') + '1') - 1)
highlights "PatIndex" and says it expects a number, a hour, a boolean (...)
Using
right(replace(totext(@Data),'.',''), len(@data) - (PatIndex(',', replace(totext(@Data),'.','') )-1) )
says there is a ) missing, highlighting PatIndex, once again.
But then I think your advice about using left/right/instr/mid functions solved my problem.
So, to get the unit I truncated.
@UNIT:
truncate({@valortotal}) and the output was 53.
To get the decimal, and since it is always no less and no more than 2 digits, I
used Right function and sinced it needed to be a string I totexted it
@DECIMAL:
RIGHT(totext({@valortotal}),2) and the output was 14.
And then I put them together (and needed to convert decimal back to number):
@VALORTOTAL:
towords({@unit}) & " euros and " & towords(tonumber({@decimal})) & " cents"
and the output was "fifty and three euros and fourteen cents".
Thank you very much for your time, member_profile.asp?PF=12744&FID=5 - comatt1
& member_profile.asp?PF=6162&FID=5 - DBlank ! :)
|
Posted By: comatt1
Date Posted: 06 Dec 2012 at 2:52am
|
Sorry about the patindex, think that will only work within SQL commands.
|
|