Hagita
asked on
Totext Formula Field
Hi All,
I need to format a field in away that will allow 6 digits precision but and with no zeros following at the right end. I am using the ToText formula filed function but still get the zeros. Iam using CR v10
this is how I use it: ToText({tblConstruction.ME S},"##.### ").
any help will do.
Thanks
I need to format a field in away that will allow 6 digits precision but and with no zeros following at the right end. I am using the ToText formula filed function but still get the zeros. Iam using CR v10
this is how I use it: ToText({tblConstruction.ME
any help will do.
Thanks
ASKER
Hi Gj, Thank you for your answer.
I added the code and got this run time error:
A subscript must be between 1 and the length of the string.
Details: errorKind
I added the code and got this run time error:
A subscript must be between 1 and the length of the string.
Details: errorKind
Please post your exact formula
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I had thought of NULLs but the formula shouldn't fail for null values bacause it will stop execution on the first line.
Interesting. It appears that if you use a format string with 2 or more "#"s for the decimal places, like "##.###", ToText/CStr still shows a minimum of 2 decimal places, even if they're both 0's. The 3rd (or later) decimal place is not shown if it's 0, but the first 2 are.
Even more interesting, CR 10 actually rounds the value to 2 decimal places when you use a format string. So, for example, ToText (20.568, "##.###") produces 20.57 (with the trailing 0 suppressed), instead of 20.568. ToText (20.568, "00.000") produces 20.570. So, trailing 0's aside, you wouldn't want to use a format string anyway, unless you want the values rounded to 2 decimal places.
The formulas posted by the others assume that you will have up to 6 decimal places. If your values are limited to 3 decimal places (somewhat implied by your ##.### format), the formulas could be changed accordingly (making them a little shorter). If your values could have more than 3 decimal places, but you only want to show the first 3, do you want to round the values, or just cut off the extra digits?
FWIW, it seems like there ought to be an easier way to remove trailing 0's, but I haven't come up with anything that I really like so far.
James
Even more interesting, CR 10 actually rounds the value to 2 decimal places when you use a format string. So, for example, ToText (20.568, "##.###") produces 20.57 (with the trailing 0 suppressed), instead of 20.568. ToText (20.568, "00.000") produces 20.570. So, trailing 0's aside, you wouldn't want to use a format string anyway, unless you want the values rounded to 2 decimal places.
The formulas posted by the others assume that you will have up to 6 decimal places. If your values are limited to 3 decimal places (somewhat implied by your ##.### format), the formulas could be changed accordingly (making them a little shorter). If your values could have more than 3 decimal places, but you only want to show the first 3, do you want to round the values, or just cut off the extra digits?
FWIW, it seems like there ought to be an easier way to remove trailing 0's, but I haven't come up with anything that I really like so far.
James
ASKER
Thank you all. My goal is to not have trailing zeros,I expect 6 or less decimal places.
mlmcc, I still get this erro when Iuse your code:
A subscript must be between 1 and the length of the string.
Details: errorKind
Failed to export the report.
Error in File crptMDTTest {EE2A0C5A-C12F-4C05-83AD-3 F88181585C 0}.rpt:
Error in formula PTOL:
'Local StringVar text ;
(I just put my field name but its exactly what you prorposed. )
mlmcc, I still get this erro when Iuse your code:
A subscript must be between 1 and the length of the string.
Details: errorKind
Failed to export the report.
Error in File crptMDTTest {EE2A0C5A-C12F-4C05-83AD-3
Error in formula PTOL:
'Local StringVar text ;
(I just put my field name but its exactly what you prorposed. )
ASKER
I noticed that the values are not null and not zeros. can it be the syntax? I am using Crystal syntax with
CR Version 10
CR Version 10
The formula is using crystal syntax.
Copy your formula here
Copy your formula here
ASKER
Local StringVar text ;
Local NumberVar end ;
Local NumberVar clip;
If IsNull({tblMMMM.ptol}) or {tblMMMM.ptol} = 0 then
"0"
Else
(
end := length ( text ) ;
text := Totext ( {tblMMMM.ptol} , 6 , "" ) ;
clip :=
(if Val ( text [ end - 6 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 5 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 4 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 3 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 2 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 1 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 0 to end ] ) = 0 then 1 else 0 ) ;
text [ 1 to Length ( text ) - clip ];
)
Local NumberVar end ;
Local NumberVar clip;
If IsNull({tblMMMM.ptol}) or {tblMMMM.ptol} = 0 then
"0"
Else
(
end := length ( text ) ;
text := Totext ( {tblMMMM.ptol} , 6 , "" ) ;
clip :=
(if Val ( text [ end - 6 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 5 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 4 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 3 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 2 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 1 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 0 to end ] ) = 0 then 1 else 0 ) ;
text [ 1 to Length ( text ) - clip ];
)
Try adding WhilePrintingRecords; as the first line of the formula.
At what point do you get the error?
At what point do you get the error?
ASKER
I tried WhilePrintingRecords. still get the error.
I get it at the point of calling the form containing the CrystalReportViewer after all definitaions are set:
its an information error message of CrystalReportViewer .
I get it at the point of calling the form containing the CrystalReportViewer after all definitaions are set:
its an information error message of CrystalReportViewer .
end := length ( text ) ;
text := Totext ( {tblMMMM.ptol} , 6 , "" ) ;
Switch the order of these 2 lines they are the wrong way around
text := Totext ( {tblMMMM.ptol} , 6 , "" ) ;
Switch the order of these 2 lines they are the wrong way around
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works!!!!! Many thanks GJ. you just made my day.
Hagit
Hagit
Why accept mlmcc incorrect formula as the solution when my original formula was correct all along ?
ASKER
You are right. Im sorry, made a mistake.
Was the original correct? You said you had the same issue with that one.
As a test take out the if part
If IsNull({tblMMMM.ptol}) or {tblMMMM.ptol} = 0 then
"0"
Else
If it works then request this be reopened so you can award properly.
mlmcc
As a test take out the if part
If IsNull({tblMMMM.ptol}) or {tblMMMM.ptol} = 0 then
"0"
Else
If it works then request this be reopened so you can award properly.
mlmcc
Local StringVar text := Totext ( {Your.NumberField} , 6 , "" ) ; //put your numeric field in this line
Local NumberVar end := length ( text ) ;
Local NumberVar clip :=
(if Val ( text [ end - 6 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 5 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 4 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 3 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 2 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 1 to end ] ) = 0 then 1 else 0 ) +
(if Val ( text [ end - 0 to end ] ) = 0 then 1 else 0 ) ;
text [ 1 to Length ( text ) - clip ]