Formating currency in a report with mils

[hallman]

I have data that I would like to format just so:

2000 to be $2,000
-2000 to be ($2,000)
2000.01 to be $2,000.01
2000.1234 to be $2,000.1234

The 'canned' Currency format type
[that comes with MS-Access 2002(10.3409.3601) SP-1]
*rounds* the fractions of a cent to the nearest cent.
I need to preserve the cents and fractions of cents,
only when they respectively exist.

My attempt at a custom format

$#,###.####;($#,###.####)
deals with the mils, but fails to
suppress the decimal point thusly

2000 becomes $2,000. not $2,000
-2000 becomes ($2,000.) not ($2,000)

I would like to suppress the decimal point
when the cents are zero.

1) Anybody know the solution to my specific problem?

2) Where is the *definitive* resource to learn about the
Format property in MS-Access reports? The {F1} help
file only brings up some abridged documentation, not
explaining all the intricacy.

[ugo_lavoie]

put you field as a text field and then you will be able to put a mask.

[hallman]

ug********@hotmail.com wrote:

put you field as a text field and then you will be able to put a

mask.

Thanks, I just tried using the mask property for my
textbox control, and I still failed trying to
supress the decimal point when I have fractions.
I also tried using the format() function, but still
needed something conditional. So...

I experimenting with another way, putting code in the onFormat
event of the Detail section of the report, I suceeded by using
this code:

===begin code===
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Int([price] * 10) = [price] * 10 _
Then ctlPrice.Format = "$#,###.00;($#,###.00);0" _
Else ctlPrice.Format = "$#,###.####;($#,###.####);0"
If Int([price]) = [price] Then ctlPrice.Format = "$#,###;($#,###);0"
End Sub
===end code===

Someone else might be able to code that more elegantly,
my self taught coding skills are crude, but it worked.