I am very much a novice at this, so bear with me.

I am using search and replace in a script to copy a sum (from a number field that is set to round off at the second decimal) in to a text field, replacing a part of the text there.

My problem is that when the number ends with a zero (ie 123.40) the zero is missing in the text (ie 123.4). These are prices, so it's confusing for users without the second decimal; an unacceptable situation.

I have the feeling there must be a very simple solution for this, but I can't find/think of it. Help!

I don't know whether this solution is simple, or if it catches all possibilities, but here's a calculation that can convert a number field into a properly formatted text field. (File attached as well...)

Case(

   not Mod(Round(number;2);1);Round(number;2)&".00";  //whole dollars get zeros added

   Mod(Mod(Round(number;2)*100;100);10);Round(number;2);  //numbers not in whole dimes just get rounded

   Round(number;2)&0  //the rest are numbers in whole dimes, so they get an extra zero

)

HTH,

Dana

roundtext.fp7.zip

You can do this "wholesale", without checking:

Int ( Amt )

& "." &

Right ( "00" & Amt * 100 ; 2 )

where Amt is non-negative and pre-rounded to 2 decimal places.

Neither one will work with copy, BTW. Try using Substitute() instead.

see, I knew there was an easier way to do it...

Thanks ever so much comment, it works like a charm. I even understand what it's doing, though I never would have thought of it on my own.

Now I have another problem, which didn't occur (to me) until now becasue I stupidly only tested this with prices of less than 1000.

I need the prices with separators, ie 1000 should read 1.000, the separating point is not being transferred from the number field when I substitute.

I did find something by searching which looks like it would be in the direction I need, but I am not sure how to apply it in my situation.

Let ( [

// USER DEFINED:

input = numberfield ;

precision = 2 ;

currency = "$" ;

separator = "," ;

decPoint = "." ;

// DO NOT MODIFY FROM THIS POINT ON

inputRound = Round ( input ; precision ) ;

Amt = Abs (inputRound ) ;

Dollars = Int ( Amt )

] ;

// PREFIX

Case ( inputRound < 0 ; "-" )

& currency

&

// BILLIONS

Choose ( Dollars < 10 ^ 9 ; Right ( Div ( Dollars ; 10 ^ 9 ) ; 3 ) & separator )

&

// MILLIONS

Choose ( Dollars < 10 ^ 6 ; Right ( Div ( Dollars ; 10 ^ 6 ) ; 3 ) & separator )

&

// THOUSANDS

Choose ( Dollars < 10 ^ 3 ; Right ( Div ( Dollars ; 10 ^ 3 ) ; 3 ) & separator )

&

// UNITS

Right ( Dollars ; 3 )

&

// FRACTION

Choose ( precision < 1 ; decPoint

&

Right ( 10 ^ precision & Amt * ( 10 ^ precision ) ; precision ) )

)

Please forgive my stupidity and help me once again! :)

It looks somewhat familiar...

You would apply this the same way as any other formula - paste it into the calculation window, change "numberfield" to the name of your field, and that's it. If you want, you can modify the other parameters marked as user-defined, e.g. change "$" to "£" or whatever currency you use.

Thank you, thank you, thank you.

I am the guy who has no real idea but a reputation for halfway logical thinking (undeserved, I am sure) at my office, so I got the job of creating our first database.

You've helped me get the last problems solved. Thank you.