• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

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
0
Hagita
Asked:
Hagita
  • 6
  • 4
  • 3
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
mlmccCommented:
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now