Currency calculations within a query

[Pure Salt]

In a query I have 'tonnes' & 'sale price' from a table.



Within the query I have created expressions to calculate the costs of VAT & totals etc. The properties are set to currency & 2 decimal places.

When it performs the calculations it uses 3 decimal places though only shows 2.

For example the sale price with vat = £2.35 & the customer had 98 bags - £2.35 * 98 = £230.30, though access shows £230.50 which means it is calculating using £2.352.

Any ideas would be really appreciated.

[ItsMe]

I use VBA. Each side of the calc gets its own variable rounded to two decimal places, using only the format() function. If you just format the result, the underlying values will not change during the calc. Format before the calc. You should be able to do the same in the SQL statement.

[rpeare]

when you do your final calculation you can do something like

((int(vat*100))/100) * 98

instead of

vat * 98

[Pure Salt]

Thanks

This works by rounding down which is fine on the ones like 3.592, but if it's 3.598 I need it to round up.

All sorted, using 'cint'

[ItsMe]

I took at look at some of my DB's and where I thought I was using format I was using the Round() function. I tried it in a query and SQL recognized it. 5 rounds down and 6 rounds up.

((Round([TableName].[Value],2))*[TableName].[Quantity]) AS MyResult


Edit: Having said that. I store currency with two decimals typically. If your quarterlies count pennies you will be off by a few pennies unless you always apply the Round() function. Not sure exactly how or why you have three decimal places in a table but maybe you can apply the round function before storing the result.

[Pure Salt]

Thank you for your help