Show 2 Decimal places without rounding

[Leesha]

Hi,
I have a query that returns numbers that will go beyond 2 decimal places. I need it to be formatted to currency which I can do. The problem is that it rounds to the next highest decimal and I need it to cut off anything after 2 decimals. For example, 2077.9375 should return $2077.93 not $2077.94. Is this possible and if so how would I format it?

Thanks,
Leesha

[StoneChucker]

Use the TRUNC function. =TRUNC(<reference>,2)

EDIT: Oops! Didn't realize this was the Access forum. I assumed Excel. Sorry!

[HansV]

Let's say that the field containing the numbers is called Amount.
Create a calculated column in the query:

Amt: Int(100*[Amount])/100

This column will contain the values truncated to 2 decimal places instead of rounded. Format the column as Currency.

[HansV]

Use the TRUNC function. =TRUNC(<reference>,2)

Hi Chris,

TRUNC is an Excel worksheet function, it is not available in Access.

[Leesha]

PERFECT!!!

Thanks,
Leesha

[Sgte]

Hi Hans,
I have a field in a table that I have designated as a currency format with 2 decimal places. However, indeed with any format I try, rounding still occurs. Very annoying - so how do I stop this please?

[HansV]

Sorry, I don't understand - how are the data rounded?

[Sgte]

the data in question is 18.83, which rounds to 19.00. I have now changed the format to decimal with Auto decimal places. This seems to have retained the data at 18.83. Still very annoying.

[HansV]

Strange - if you set the data type to Currency, the default format should be Currency and decimal places should be set to Auto. The field should then use your Windows setting for currency:

x83.png

x84.png