riddle me this...I've got a 3 price fields for my products which are sold in 3 different size packages. When I calculate these prices into one text field, I'm losing all of the currency formatting of .00. for example:

small $1.00

medium $2.00

large $3.00

my calc'd field is resulting in: small $l, medium $2, large $3.

what I want to have is: small $1.00, medium $2.00, large $3.00.

thanks in advance,

Roger

The original fields are number fields are they?

** The original fields are number fields are they? -- Vaughan Bromfield, Sydney, Australia **

Yes.

Roger

[ April 03, 2002, 04:48 PM: Message edited by: graphex ]

Try this calc:

"Small"&" "&"$"&NumToText(Int(Small))&

Case(Mod(Small,1)=0,".00",

Mod(Small,1) > 0 and Length(NumToText(Mod(Small,1)))=2,NumToText(Mod(Small,1))&"0",

Mod(Small,1) > 0 and Length(NumToText(Mod(Small,1)))=3,NumToText(Mod(Small,1)))&", "&

"Medium"&" "&"$"&NumToText(Int(Medium))&

Case(Mod(Medium,1)=0,".00",

Mod(Medium,1) > 0 and Length(NumToText(Mod(Medium,1)))=2,NumToText(Mod(Medium,1))&"0",

Mod(Medium,1) > 0 and Length(NumToText(Mod(Medium,1)))=3,NumToText(Mod(Medium,1)))&", "&

"Large"&" "&"$"&NumToText(Int(Large)&

Case(Mod(Large,1)=0,".00",

Mod(Large,1) > 0 and Length(NumToText(Mod(Large,1)))=2,NumToText(Mod(Large,1))&"0",

Mod(Large,1) > 0 and Length(NumToText(Mod(Large,1)))=3,NumToText(Mod(Large,1))))

Hope this helps

Here's a slightly "cleaner" calc:

"small $" & Left(NumToText(smallAmt + .001), Length(NumToText(smallAmt + .001)) - 1) &

"medium $" & Left(NumToText(medAmt + .001), Length(NumToText(medAmt + .001)) - 1) &

"large $" & Left(NumToText(largeAmt + .001), Length(NumToText(largeAmt + .001)) - 1)

The way it works is, we add .001 to the numbers to ensure they have decimal digits, then we lop off that trailing "1" by first changing the number to text, then selecting only what is to the left of that trailing 1, that is, the length minus one.

code:

---

CURRENCYSIGN & " " &

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 ))

Where in uppercase are variables.

This one would deal with decimal virgola too.

(Result from discussion FMExperts list)

HTH

Dj

Dj, that's the second time today our posts have crossed in the mail! But amazingly, I think my solution is better for once. ciao

To slstrother, Fitch & dj,

Thanks a lot!!!

Regards,

Roger

Holy cow! i could not believe the LENGTH of the calculation to accomplish something that in theory should be a simple task.

Oh well, so much for my perfect world.