Currency Format

[syncdba]

I have a column as AMOUNT which I Converted to Money
AMOUNT MONEY
50000 $50,000.00
200000 $200,000.00
10 $10.00
7000000.0 $700,000.00
Now, I'm trying to get MONEY in the following
USD .5M(In Millions)
USD 6M
USD .0001M
USD 7M
I hope I have put decimal in correct places.

 

[lbass]

No, I don't think the decimals are in the right places. Anyway, I think you have to do this in two steps. First create a formula {@millions}:

tonumber({@Money}/1000000)

Or use:

{table.amount}/1000000

Then right click on this->format field->number->customize. Set rounding to: .0001. Then set decimals to 1.0000 and go into the x+2 formula area there and enter:

if val(right(totext({@millions},4)4))=0 then 0 else
if val(right(totext({@millions},4)3))=0 then 1 else
if val(right(totext({@millions},4)2))=0 then 2 else
if val(right(totext({@millions},4)1))=0 then 3 else 4

Then insert a text box and add the text "USD " and then add the formula {@millions} that you have just formatted, followed by "M".

-LB

 

[syncdba]

Its giving me error" The ) is missing" I couldnt find out the error.BTW I'm using CRXI
if val(right(totext({@millions},4)4))=0 then 0 else
Giving Error near 4

 

[lbass]

Sorry, that should be:

if val(right(totext({@millions},4),4))=0 then 0 else
if val(right(totext({@millions},4),3))=0 then 1 else
if val(right(totext({@millions},4),2))=0 then 2 else
if val(right(totext({@millions},4),1))=0 then 3 else 4

A comma should precede the second number in each row.

-LB