Totext Formula Field - need help with formatting

Hi all, I have a field formula that I use as discribed in this case:
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27319603.html

I need to change the formula so that there will be a zero digit before the decimal place if the value is less then 1,I tried and cannt make it work.

Thanks, Hagit
HagitaAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
You must have the default number format set to not show leading 0s.

I just created a new report.
Modified your formula to use 0.25 as the number and eliminated the test for NULL
Ran the report got
0.25 as the result

I then changed the defaults (FILE --> OPTIONS  on the FIELDS TAB customized number to not show leading 0.
Added the same formula got
 .25

Try this
Open Crystal with NO report open
Click FILE --> OPTIONS
Click the FIELDS tab
Click the NUMBER button
Click the NUMBER tab
Click CUSTOM STYLE
Click CUSTOMIZE
Set LEADING 0 on
Close the popups
Open your report and see if it works.
I just did that and my report changed from showing .25 to 0.25

If that doesn't work or you want to leave the defaults as they are since that will change other formatted fields use the modified code below where I forace a leading 0 if needed.

mlmcc
Local StringVar text ; 
Local NumberVar end ; 
Local NumberVar clip;
WhilePrintingRecords;
If IsNull({tblMMMM.ptol}) or {tblMMMM.ptol} = 0 then
   "0"
Else
(
  
    text     :=  Totext ( {tblMMMM.ptol} , 6 , ""  )  ;
    If Left(text,1) = '.' then
       text := '0' & text;    end  :=  length ( text ) ; 
    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 ];
)

Open in new window

0
 
mlmccCommented:
Not sure what you are using as the formula

To have a 0 at the front you can use

ToText({YourNumberString},"#0.##")

If that isn't helpful, please post the formula you are using.

mlmcc
0
 
James0628Commented:
So, you're saying if the field is .25 (less than 1), you want to see 0.25 (0 before the decimal)?

 If you're using the formula that you posted in that question, you should already be getting that.  The core of that formula is Totext ( {tblMMMM.ptol} , 6 , ""  ), and that produces a 0 before the decimal if the number is between 0 and 1 (or 0 and -1).  If you're not getting the 0, you must have changed something in the formula.  Can you post it so that we can take a look at it?

 James
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
HagitaAuthor Commented:
Local StringVar text ;
Local NumberVar end ;
Local NumberVar clip;
WhilePrintingRecords;
If IsNull({tblMMMM.ptol}) or {tblMMMM.ptol} = 0 then
   "0"
Else
(
 
    text     :=  Totext ( {tblMMMM.ptol} , 6 , ""  )  ;
    end  :=  length ( text ) ;
    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
 
mlmccCommented:
If you have a formula like

Totext ( {tblMMMM.ptol} , 6 , ""  )

Do you get the leading 0?

mlmcc
0
 
mlmccCommented:
Does this give you what you want

Totext ( {tblMMMM.ptol} ,  "0.0#####"  )

mlmcc

0
 
James0628Commented:
If I take the formula that you posted and replace {tblMMMM.ptol} with .25, I get "0.25".  That sounds like what you said you wanted.  If it's not, can you explain again what you're looking for?

 James
0
 
HagitaAuthor Commented:
I get .25 and need to have 0.25.
I am sorry that I cannt look into it right now as I am on a holiday and out of the office until next week.
thank you both so much.
Hagit
0
 
mlmccCommented:
Better yet, upload the report.

mlmcc
0
 
James0628Commented:
I don't know how you could get ".25" with that formula.  I'm using CR 10.  I suppose if you're using a different version, ToText might be operating a little differently.

 I know that you're out of the office, but when you get back, try creating a formula like the following and see what it produces:

Totext (.25, 6, "")


 I get "0.250000".

 James
0
 
James0628Commented:
Interesting.  I don't think it ever would have occurred to me that the default number formatting would have affected ToText.

 James
0
 
mlmccCommented:
I didn't expect it to have that effect but it was the only thing left to look at.

mlmcc
0
 
HagitaAuthor Commented:
I dont know how to thank you guys. I used mlmcc's fix of the formula and finally it works.
I couldnt find the Options on the File menu.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.