Solved

# Totext Formula Field - need help with formatting

Posted on 2011-10-11
447 Views
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
Question by:Hagita

LVL 100

Expert Comment

Not sure what you are using as the formula

To have a 0 at the front you can use

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

mlmcc
0

LVL 34

Expert Comment

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

Author Comment

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

LVL 100

Expert Comment

If you have a formula like

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

Do you get the leading 0?

mlmcc
0

LVL 100

Expert Comment

Does this give you what you want

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

mlmcc

0

LVL 34

Expert Comment

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

Author Comment

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

LVL 100

Expert Comment

mlmcc
0

LVL 34

Expert Comment

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

LVL 100

Accepted Solution

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.
.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
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 ];
)
``````
0

LVL 34

Expert Comment

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

James
0

LVL 100

Expert Comment

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

mlmcc
0

Author Comment

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

### Suggested Solutions

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…