Format problem in Access reports/forms

[rayc]

My Access (adp) file has reports and forms that fetch data from an SQL Server table that has a UnitPrice field with data type decimal(20,5). When my reports generate, the Format property and Decimal property seem to get ignored by Access for this field. The Textbox has Format=Currency and Decimal=2. But the report shows for example 1.50000 instead of $1.50.



The reason why the UnitPrice field has 5 decimals is because in some cases we have values of 0.01395 as a unit price (i.e. paper clips). We need 5 decimals so that the report rounds off better when showing the total on the invoice. So I would like for it to show $0.01395 when all 5 decimals are relevant. But in cases where a unit price is just 1.50, I would like to see $1.50 on the report, not 1.50000.

Can anyone tell me how to do this in the reports?

Thanks in advance

[pbaldy]

My guess is that something is causing the field to be viewed as text. You can try wrapping in the CCur() function, or one of the others.