Query Calculation Issue

Question

I'm calculating the product of two fields within a simple query - general number format with 2 decimal places. The results are all fine as long as one of the operands is an integer or contains the following decimals, .25, .5 and .75. The other operand is always an integer.

If the decimal is any other value the decimal extends to 14 places such that 2*3.35 becomes 6.69999980926514 instead of 6.7.

I read a recent post 6/23 or 6/24 that sounded similar but the responses recommended that she should be using a currency field. This is not a currency calculation. Thanks for any help.

Regards,

Richard

Answer 1

The CURRENCY datatype is indeed primarily used for money transactions, but it is not limited to JUST money transactions. It's a scaled huge integer, with exactly four digits after the decimal, and no roundoff error such as you are seeing. A DOUBLE number datatype is (of necessity) an approximation; some numbers (even 0.1) cannot be represented exactly as a binary fraction, so you'll get the 14-digit expression you see.

Either use the Round() function to round the calculation to the desired precision, or (if four decimal places is enough) use a Currency datatype. The Currency Format is separate - since you don't want to see dollar signs simply use a different Format for the field.

Answer 2

You should call the Round function in the query, not format the column e.g.

SELECT Round(Column1 * Column2,2) AS Result

FROM YourTable;

Any values which round to an integer will be returned as such,  others will be returned to one decimal place if the second decimal place is a zero, or to two decimal places otherwise.

Answer 3

I've given up on this issue for now. Thanks for everyone's help. Since this is a personal database I just adjusted the source table data and made a note. Since this looks buggy to me, I'll wait and see if things change with upcoming Access updates.

To recap:

In a simple query, I was calculating the product of two columns into a third column. All results calculated and displayed properly, under the General Number - Auto Decimal setting, except a single field that displayed a 14 digit decimal (3.35 * 2 = 6.69999980926514). Experimenting with the first field, which is a non-calculated field in the source table, I discovered that 3.25, 3.50 and 3.75 yield the expected result, of 6.5, 7, and 7.5, in my query. Any other decimal that I tried yields the 14 digit decimal.

I tried rounding the result in the third column of the query - Round (field1 * field2, 2) - which resulted in the same 14 digit decimal. If I changed the decimal argument in the Round Expression to - ,0 - it calculated a proper result of 7.

Interestingly, if I use a Round Expression on the first non-calculated query column, 3.35 displays as 3.4999990463257 and all the other fields in that column display properly.

When using the Fixed or Standard number format all results display properly.

I'm using the most recent version of Access 2016 from Office 365.

Answer 4

You can format the column to only show as many decimal places as you want. Or you can use the Round function to limit the number of decimal places.

Answer 5

This is ultimately a result of the system doing decimal calculations (i.e. to base 10) using binary arithmetic (i.e. to base 2).  The latter cannot always produce the exact result of the former.  The currency data type is merely a data type to a precision of four significant decimal places.  This is designed to eliminate as far as possible rounding errors in currency calculations, whose values are in day to day use normally expressed to two decimal places of course, most currency systems having 100 secondary units per primary unit, e.g.  US dollars/cents, GB pounds/pence etc.  Financial markets use a higher precision.

Whether you use the currency data type or a data type of a higher precision depends on whether a precision of four significant decimal places is sufficient to eliminate rounding errors in the types of arithmetical operations you are undertaking, bearing in mind that these might be cumulative.  If not, do not use the currency data type, but for presentational purposes  format the values rounded to the precision in which you wish to see the data.  The underlying arithmetic will be done at the higher precision, but you'll see the results rounded.  With cumulative arithmetical operations, as with currency arithmetic, you have to decide at which stage(s) it is appropriate to apply rounding rather than mere formatting, i.e. reducing the precision of the actual values before the next stage in a cumulative calculation is undertaken.