Link to home
Start Free TrialLog in
Avatar of biotec
biotec

asked on

Crystal Report formula to only return whole number from decimal

Currently the report has two fields and the formula is shown below for pulling each number. The problem is the number in the db was changed to a decimal so it generally returns something like 42.00 instead of 42. I need to strip out the decimal and the characters to the right. Second problem is I'm not familiar with this formula as it was written by someone else so not sure how to edit it either. If it requires a new way to do the same thing without using this that is fine. Thanks

Local StringVar Message1 :="";

if (Length({chdp_pm160_.bmi})=2)
then Message1 := {chdp_pm160_.bmi};

if (Length({chdp_pm160_.bmi})=1)
then Message1 := "0"+{chdp_pm160_.bmi};

Message1[1 to 1]

Local StringVar Message1 :="";

if (Length({chdp_pm160_.bmi})=2)
then Message1 := {chdp_pm160_.bmi};

if (Length({chdp_pm160_.bmi})=1)
then Message1 := "0"+{chdp_pm160_.bmi};

Message1[2 to 2]
Avatar of vasto
vasto
Flag of United States of America image

Try to use totext as is it show bellow (with 0 for number of characters after decimal)
Local StringVar Message1 :=totext({chdp_pm160_.bmi},0);
The other part of the formula should be changed to use Message 1

Local StringVar Message1 :=totext({chdp_pm160_.bmi},0);
if (Length(Message1 )=1) then Message1 := "0"+Message1;


The same for the other one
Avatar of biotec
biotec

ASKER

I did what I thought you were saying but it throws an error. Too many arguments given to this function.

Local StringVar Message1 :=totext({chdp_pm160_.bmi},0);
if (Length(Message1 )=2) then Message1 := Message1;

if (Length(Message1)=1)
then Message1 :=  "0"+Message1;

Message1[1 to 1]
Try this

Local StringVar strNum := Split({chdp_pm160_.bmi}, '.');
Local StringVar Message1 :="";

 if (Length(strNum )=2)
 then Message1 := strNum ;

 if (Length(strNum)=1)
 then Message1 := "0"+strNum;

 Message1[1 to 1]

Open in new window


Local StringVar strNum := Split({chdp_pm160_.bmi}, '.');
 Local StringVar Message1 :="";

 if (Length(strNum )=2)
 then Message1 := strNum ;

 if (Length(strNum )=1)
 then Message1 := "0"+strNum ;

Open in new window


mlmcc
vasto's suggestion is giving you that error because he's trying to convert the bmi field from a number to a string, but it's already a string.  You can actually use ToText on a string (eg. ToText ("abc") ), but when you try to add arguments to format a numeric value (eg. ToText ("abc", 0) ), you'll get that error.

 mlmcc's suggestion will probably work.  It uses Split to extract the part of the string before the decimal point (if there is one) and then handles that part like normal (ie. adds a leading "0" if it's only 1 digit).  But he left out the Message1[2 to 2]  that should have been at the end of the second formula.

 James
Avatar of biotec

ASKER

I get an error This array must be subscripted. For example: Array.  I'm thinking that it would be easier to just eliminate the decimal and anything after the decimal. I have two spaces to populate If there is only one number I add a zero. I guess the Message1[1to1] thing is confusing me.
Sorry forgot to add the subscripts

Local StringVar strNum := Split({chdp_pm160_.bmi}, '.');
Local StringVar Message1 :="";

 if (Length(strNum[1] )=2)
 then Message1 := strNum[1] ;

 if (Length(strNum[1])=1)
 then Message1 := "0"+strNum[1];

 Message1[1 to 1]

Open in new window


Similarly for the other one

mlmcc
Avatar of biotec

ASKER

Sorry, still same error. The formula posted doesn't look different than the first one, maybe just an errant copy past? The error highlights the end of the first line.
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of biotec

ASKER

No errors but still not working and I think it's because my field is a varchar(5).
What isn't working?

mlmcc
Avatar of biotec

ASKER

My bad it works ok, just had to change the field type.
I guess the Message1[1to1] thing is confusing me.
If you don't know, Message1[1 to 1] and Message1[2 to 2] just extract the first and second characters, respectively, from the string in Message1.  CR will let you treat a string like an array of single characters.  For example, "abcde" [4] will give you "d".  You can also ask for a range of characters.  For example, "abcde" [3 to 4] will give you "cd".

 So, Message1[1 to 1] extracts the first character from Message1, and Message1[2 to 2] extracts the second character.  But the range in those is completely unnecessary, since it's only 1 character.  You could just use Message1[1] and Message1[2] instead.

 James
Avatar of biotec

ASKER

That was extremely helpful. If I could give you points I would. :)
No problem.  :-)

 James