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]
Local StringVar Message1 :="";
if (Length({chdp_pm160_.bmi})
then Message1 := {chdp_pm160_.bmi};
if (Length({chdp_pm160_.bmi})
then Message1 := "0"+{chdp_pm160_.bmi};
Message1[1 to 1]
Local StringVar Message1 :="";
if (Length({chdp_pm160_.bmi})
then Message1 := {chdp_pm160_.bmi};
if (Length({chdp_pm160_.bmi})
then Message1 := "0"+{chdp_pm160_.bmi};
Message1[2 to 2]
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]
Local StringVar Message1 :=totext({chdp_pm160_.bmi}
if (Length(Message1 )=2) then Message1 := Message1;
if (Length(Message1)=1)
then Message1 := "0"+Message1;
Message1[1 to 1]
Try this
mlmcc
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]
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 ;
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
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
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
Similarly for the other one
mlmcc
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]
Similarly for the other one
mlmcc
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No errors but still not working and I think it's because my field is a varchar(5).
What isn't working?
mlmcc
mlmcc
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
ASKER
That was extremely helpful. If I could give you points I would. :)
No problem. :-)
James
James
Local StringVar Message1 :=totext({chdp_pm160_.bmi}
The other part of the formula should be changed to use Message 1
Local StringVar Message1 :=totext({chdp_pm160_.bmi}
if (Length(Message1 )=1) then Message1 := "0"+Message1;
The same for the other one