formatting number within a calc

[kiwiora]

Hi guys,

I understand how to format a number field. What my problem is when i then include it in a calc it seems to be dropping a lot of the formatting. An example follows

field: LoanAmt

number entry: 5200

formatted entry: 5,200.00

in my calc i have:

If(Loan = "Yes", "This contract is subject to finance in the amount of $" & NumToText(LoanAmt) & " from " & LoanLender & " approval of which is due by " & DateToText (LoanAppDueDate), "") & If(SpecialConditions <> "", "" & SpecialConditions, "")

My problem is when the number is a .00 it just puts in the number with no decimal or thousands separator. i.e 2.56 works fine. 2.00 does not and only puts in 2, likewise 5000.56 but it should be 5,000.56

Even if i type in the comma and the decimal it ignores it in the calc. The other thing i guess is to change the number field to a text field ... and that will work fine in the calc, but it wont' format the field if the user types it in as 5000

[BobWeaver]

Why don't you display the field as a merge field and use the number formatting options to show 2 decimal places and the thousands separator?

[kiwiora]

I can't do that because i need to export the result externally as one field

[BobWeaver]

I was afraid of that. Have a look at this thread:

http://www.fmforums.com/threads/showflat.php?Cat=&Number=85370&page=3&view=collapsed&sb=5&o=31&fpart=1

The issue is covered fairly thoroughly. Since you won't likely need to worry about negative numbers, you may be able to use one of the simpler solutions.

[-Queue-]

Ah, yes. I remember it well.

Make it stop! Make it stop! Dear God, please MAKE IT STOP!

[Lee Smith]

Hi Queue,

Some things are just to "interesting" to die a natural death.

[Ugo DI LUCA]

I was afraid of that too Bob, and I'm not sure it may be easy here too as he wants the "," to separate thousands...

Let see if this would work :

Case(TextToNum(NumberText)>1000,

Left(NumberText,Length(Int(NumberText / 1000))) & "," & Middle(Int(Round(ABS(NumberText), 2)),Length(Int(NumberText / 1000))+1,14) & "."& FIN(100+MOD(Round(ABS(NumberText)*100,0),100),2),Int(Round(ABS(NumberText), 2))& "."& FIN(100+MOD(Round(ABS(NumberText)*100,0),100),2))

So that 152489.896 ==> 152,489.90

Largely isnpired by the thread you mentionned, that had ruined my week-end

Please all, say it again...STOP.

[BobWeaver]

I thought somewhere near the end of the thread, Dj had posted a method that handled the thousand separator. Does this mean I have to go back and read the whole thing over again? Aaaaaahhh!

[BobWeaver]

This is a bit simpler but only works with non-negative numbers:

Case( Int(NumberEntry/1000), Int(NumberEntry/1000)&","&Right("000"&Mod( Int(NumberEntry),1000),3),Mod( Int(NumberEntry),1000) ) & "." &

Right("00"&Round((NumberEntry-Int(NumberEntry))*100,0),2)

Also, it only puts in one comma. If the number is bigger than 999,999.99, then the formula needs to be modified to add another comma.

Here we go again.

[Ugo DI LUCA]

Somewhere near the end ?

You mean the 4th page I had never read before...

These ^ just make me sick

Case(TextToNum(NumberText)>1000,

Left(NumberText,Length(Int(NumberText / 1000))) & "," & Middle(Int(Round(ABS(NumberText), 2)),Length(Int(NumberText / 1000))+1,14) & "."& Right(100+MOD(Round(ABS(NumberText)*100,0),100),2),Int(Round(ABS(NumberText), 2))& "."& Right(100+MOD(Round(ABS(NumberText)*100,0),100),2))

just replaced the "FIN" which is Right() in French

[Ugo DI LUCA]

BobWeaver said:

Here we go again.

Where ?

[kiwiora]

oh no did i open up a can of whoop ass.. lol

Thanks guys i'll read up when I get back to work in the morning. Such a simple thing really you'd think fmp would deal with it...

The secret to finding stuff on this forum is knowing the keywords to search for!

[BobWeaver]

At the very end of that thread, Dj did post a method that handles the commas.