Solved

Totext Formula Field

Posted on 2011-09-21
18
1,105 Views
Last Modified: 2012-05-12
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.MES},"##.###").
any help will do.
Thanks
0
Comment
Question by:Hagita
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 19

Expert Comment

by:GJParker
ID: 36574217
Credit to Ken Hamady for this one

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 ]

0
 

Author Comment

by:Hagita
ID: 36578878
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

0
 
LVL 19

Expert Comment

by:GJParker
ID: 36580534
Please post your exact formula
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 125 total points
ID: 36580599
Can you copy and paste the formula from rhe report?
I can't make the one provided fail.

One thought.  Can the number field be NULL?
Local StringVar text ;
Local NumberVar end ;
Local NumberVar clip;
If IsNull({Your.NumberField}) or {Your.NumberField} = 0 then
   "0"
Else
(
    end  :=  length ( text ) ;
    text     :=  Totext ( {Your.NumberField} , 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 ];
)

mlmcc
0
 
LVL 19

Expert Comment

by:GJParker
ID: 36580678
I had thought of NULLs but the formula shouldn't fail for null values bacause it will stop execution on the first line.
0
 
LVL 34

Expert Comment

by:James0628
ID: 36588511
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
0
 

Author Comment

by:Hagita
ID: 36594920
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-3F88181585C0}.rpt:
Error in formula  PTOL:
'Local StringVar text ;
(I just put my field name but its exactly what you prorposed. )
0
 

Author Comment

by:Hagita
ID: 36594963
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
0
 
LVL 19

Expert Comment

by:GJParker
ID: 36594965
The formula is using crystal syntax.

Copy your formula here
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Hagita
ID: 36594966
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 ];
)
 
0
 
LVL 19

Expert Comment

by:GJParker
ID: 36594971
Try adding WhilePrintingRecords; as the first line of the formula.

At what point do you get the error?
0
 

Author Comment

by:Hagita
ID: 36594990
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 .
0
 
LVL 19

Expert Comment

by:GJParker
ID: 36595017
    end  :=  length ( text ) ;
    text     :=  Totext ( {tblMMMM.ptol} , 6 , ""  )  ;


Switch the order of these 2 lines they are the wrong way around
0
 
LVL 19

Assisted Solution

by:GJParker
GJParker earned 125 total points
ID: 36595018
    end  :=  length ( text ) ;
    text     :=  Totext ( {tblMMMM.ptol} , 6 , ""  )  ;


Switch the order of these 2 lines they are the wrong way around
0
 

Author Comment

by:Hagita
ID: 36595043
It works!!!!! Many thanks GJ. you just made my day.

Hagit
0
 
LVL 19

Expert Comment

by:GJParker
ID: 36595061
Why accept mlmcc incorrect formula as the solution when my original formula was correct all along ?
0
 

Author Comment

by:Hagita
ID: 36595070
You are right. Im sorry, made a mistake.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36595292
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

864 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now