Forcing 2 zeros after a decimal point

[Dagel]

I have a calculation field whose result is text. Integrated into this calculation I have a number field. Here's the problem: When the two digits to the right of the decimal in that number field are both zeros, only one of them appears. I've tried the Round function to no effect. Am I just a knucklehead or has anyone else had this problem?

[QuinTech]

As it happens, i've just had to deal with this issue this week. Here is the calc i used:

Case(

  patterncount(theField,".")=0,

   theField & ".00" , 

  left(right(theField,2),1)=".",

   theField & "0" , 

   NumToText(Round(TextToNum(theField),2))

)

So in the first case, if there is no decimal, the calc adds ".00". If there is one digit after the decimal, the calc adds "0"; otherwise, it just returns the number rounded to two digits.

This assumes theField is a text field -- if it's not, use NumToText(theField) instead of theField and theField instead of TextToNum(theField).

HTH,

Jerry

[Lee Smith]

Hi Dagel,

If I'm understanding your question correctly, you want to have a NumberToText calculation that will not drop the zero decimals. If so, this question comes up from time to time. In fact, if you did a search for +text +number +decimal of "All Forums" and blank in the "Weeks", you will see 118 hits. Here is a recent post

Hi Jerry,

Your calculation will break on 100 and a couple of other test numbers I have to test this problem. I keep looking for a solution, but so far, all have broken under one or more circumstance.

As I stated in my post (the link above), the only 100 percent accurate way is with the second layout to copy the formatted number from, for pasting into a text field.

HTH

Lee

[comment]

Would you willing to test this?

Int ( Round ( InputNumber , 2 ) )

& "." &

Right ( "00" & Round ( 100 * Mod ( InputNumber , 1 ) , 0 ) , 2 )

[Ugo DI LUCA]

Also,

here's a magic calc from Dj a while back for any number of decimals you wish, which would even account for thousand separators.

his one will put thousand separator too

Case( NUMBER < 0 , "-")

&

Choose( Abs( Round( NUMBER , PRECISION )) < 10^9 ,

Right( Abs(Int( Round( NUMBER , PRECISION ) / 10^9 )) , 3 ) & "," )

&

Choose( Abs( Round( NUMBER , PRECISION )) < 10^6 ,

Right( Abs(Int( Round( NUMBER , PRECISION ) / 10^6 )) , 3 ) & "," )

&

Choose( Abs( Round( NUMBER , PRECISION )) < 10^3 ,

Right( Abs(Int( Round( NUMBER , PRECISION ) / 10^3 )) , 3 ) & "," )

&

Right( Abs( Int( Round( NUMBER , PRECISION ))) , 3 )

&

Choose( PRECISION = 0 , "." &

Right( 10^PRECISION & Abs( Round( NUMBER , PRECISION ) )

* ( 10^PRECISION ) , PRECISION ))

NUMBER and PRECION are numbers (no kidding)

Just enter the number of digits you want and it will do it for you.

[Lee Smith]

Hi comment,

Excellent. I have a couple of test files, but the one I used before didn't find an error.

Ugo,

I think I have that one somewhere from before and it did break if I recall. I will look for the file and get back to you.

Life is sweet.

Lee

[Ugo DI LUCA]

Never seen it break Lee.

But comment's calc turns to be false with negative inputs

(1.52) becomes (1.48)

[Lee Smith]

That dosen't happen in v6 and 9.2 OS?? -1.52 is -1.52 and (1.52) is -1.52???

[comment]

You didn't specify negative input. Better make that:

Left( "-", InputNumber < 0) &

Int( Round( Abs( InputNumber) , 2 ) )

&"." &

Right( "00" & Mod(Int(Round(Abs(InputNumber), 2)*100), 100) , 2 )

Actually, the principle is the same as in Ugo's.

[comment]

Seems to me that

Choose( PRECISION = 0 , "." &

Should be

Choose( PRECISION <= 0 , "." &

The way it is now, you are getting a superfluous decimal point when precision is negative.

E.g. 123456.78 with precision = -3 produces "123,000.".

[comment]

And I think mine can be shortened to:

Left( "-", InputNumber < 0)

&

Int( Round( Abs( InputNumber) , 2 ) )

& "." &

Right( "00" & Round(Abs(InputNumber) * 100 , 0 ) , 2 )

[Lee Smith]

Hi Comment,

The first one works better. This one yield the number as a negative number if the user enters $ or + sign in front of it.

Lee

[comment]

I don't understand - the user enters $ or + sign where?

InputNumber is a number field - you're not supposed to put dollar signs in it. But even if you do, it shouldn't matter (it doesn't when I try it).

I thought the way it's supposed to work is like this:

"some text " & the formula & " another text".

If you want currency formatting, you need to add that to the formula, AFTER the negative sign.

[Lee Smith]

Hi Comment,

Sorry to be so confusing.

I was testing both text and number impute. And of course you are correct about the number impute.

Lee

[Dagel]

Thanks to everyone for the help-elegant solutions all.

[QuinTech]

Ugo & comment: Very nice formulae. Thanks for correcting my (unknown) issue!

[comment]

Arghh! Now that I read it again, I see it can be shortened even more:

Int ( Round ( InputNumber , 2 ) )

& "." &

Right ( "00" & Round ( Abs ( InputNumber ) * 100 , 0 ) , 2 )

[MorFologist]

I had the urge to add commas (up to a 7 digit number), but I'd love to see a trimmer way to do that.

Let ( [_TheAmount = YourTable::YourNumHere ; _WholeNumber = Int ( Round ( _TheAmount , 2 ) ) ];

case(

length( _WholeNumber)=3;_WholeNumber;

length( _WholeNumber)=4; Left ( _WholeNumber ; 1 ) & "," & Right ( _WholeNumber ; 3 );

length( _WholeNumber)=5; Left ( _WholeNumber ; 2 ) & "," & Right ( _WholeNumber ; 3 );

length( _WholeNumber)=6; Left ( _WholeNumber ; 3 ) & "," & Right ( _WholeNumber ; 3 );

length( _WholeNumber)=7; Left ( _WholeNumber ; 1 ) & "," & Middle ( _WholeNumber ; 2 ; 3 )& ","& Right ( _WholeNumber ; 3 )) &

"." &

Right ( "00" & Round ( Abs ( _TheAmount ) * 100 , 0 ) , 2 )

)