Round and round we go!

[spongebob]

Hi Forum!

Yes, I could find and program my own solution here, but, I decided, before my brain goes up in smoke on such a "banale" issue, I thought Id ask the forum as I think only 2 billion of us surely must have solved this one before. This is a problem as annoying as it is simple.

I have a totally simple calculation field that has a text as a result.

I do this:

"Billtotal: $" & Round(bill_total;2)

Goes without saying; bill_total is of course, a number field.

I want it to print: Billtotal: $20.45

Problem: if the number bill_total is 20, this expression above gives

Billtotal: $20

and not as desired:

Billtotal: $20.00

Same if the number bill_total is 20.5, this gives

Billtotal: $20.5

and not as desired:

Billtotal: $20.50

What gives? What Syntax could correct this? Ive tried all variations of getastext or getasnumber it seems...any ideas / code that can do this in a calculation field?

Can we have a competition as to who might provide the most efficient way?

Thanks in advance!

[comment]

See if this helps:

http://fmforums.com/forum/showpost.php?post/321299/

[spongebob]

Hi there thank you that was very useful.

That thread suggests that this is the solution:

Let (

a = Round ( YourField ; 2 )

;

Int ( a ) & SerialIncrement ( ".00" ; 100 * Mod ( a ; 1 ) )

)

However, this assumes that the amount will never be negative. And that one means TWO decimal places, not 100.

So for completion; I believe this will work correctly for negatives as well (with only 2 decimal places!).

If(Myvalue<0; "-";"")&

Let(

a = Round( Abs(Myvalue) ; 2);

Int( a ) & SerialIncrement (".00" ; 100 * Mod(a ; 1 ))

)

Am I right? (I make no guarantees here... )

Thanks again,

Best Wishes

Wumpus

[comment]

Well, not exactly - because a value can change sign as a result of rounding. Your formula would display -0.001 as "-0.00". So I would suggest =

Let ( [ 

r = Round ( YourField ; 2 ) ;

a = Abs ( r ) 

] ;

Case ( r < 0 ; "-" ) &

Int ( a ) & SerialIncrement ( ".00" ; 100 * Mod ( a ; 1 ) )

)

[spongebob]

Rock and roll!

Thank you!