Inserting a comma in a text field

[Rich S]

Howdy, howdy:

I have the following calculation:

Case (

IsEmpty (Total_Distance); "";

Choose_SAE_or_Metric = "Metric"; "km/h " & TextColor("(" & Round (Total_Distance * .6213712; 1) & " mi/h)"; RGB (0;0;255));

Choose_SAE_or_Metric = "SAE"; "mi/h " & TextColor("(" & Round (Total_Distance * 1.609344; 1) & " km/h)"; RGB (0;0;255))

)

...where I want to have FM automatically insert a comma should the calculated number be over 999. Since this has to remain as a text field, what would be the calculation to add for inserting a thousands-place comma? I thought of using the "Right" function, but got nowhere with it.

TIA for your help!

Rich

[fabriceN]

Try NumToJText ( number ; 1 ; 1 ), then substitute the unicode characters by standard digits.

Edited April 30, 2008 by Guest

[Rich S]

Thanks! It worked as advertised.

[Raybaudi]

Try NumToJText ( number ; 1 ; 1 )

Nice, I didn't know...

Why not directly: NumToJText ( number ; 1 ; 0 ) ?

BTW: For European numbers, there isn't anything better than:

Substitute ( NumToJText ( Int ( number ) ; 1 ; 0 ) ; "," ; "." ) & If ( number - Int ( number ) ; Abs ( number - Int ( number ) ) ) ?

[comment]

Ah, you have found it - I was hoping to keep it a secret...

BTW, I would use Mod ( number ; 1 ) instead of number - Int ( number ).

[Raybaudi]

BTW, I would use Mod ( number ; 1 ) instead of number - Int ( number ).

What if number is negative ?

[comment]

Number is never negative. You would use Abs() before everything else and deal with the minus sign explicitly (check the result of your version with -0.5 as the number).

[fabriceN]

Why not directly: NumToJText ( number ; 1 ; 0 )

couldn't find it anymore, thanks.

There is also a trick that I like : place your number field on a layout, go to format/number menu, format the field as you like, and get the result in a GetLayoutObjectAttribute calc.

Easy and powerful, not very dynamic though :