-1

This is not a formatting question.

I have recently found that doing a simple division in an MS Access (2010) query does not return the expected number of digits. Even the simplest: (3.1 + 3.1)/2 = 3.1

But MS Access returns 3.09999990463257

Yes, I can round and/or format the result to show a specified number of decimal places. Also, in the example above, rounding would give me the true value of 3.1; however, my question is simply "why does MS Access do this?" rather than how to fix it.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
random13
  • 15
  • 7
  • 2
    Possible duplicate of [Is floating point math broken?](http://stackoverflow.com/questions/588004/is-floating-point-math-broken) – Gord Thompson Feb 05 '16 at 17:08
  • That link will help you understand the issues involved with decimal math on a binary system. But something else in your question surprises me. The following Access 2010 query shows me 3.1 for Expr1 and -1 (True) for Expr2. `SELECT (3.1+3.1)/2 AS Expr1, (3.1+3.1)/2=3.1 AS Expr2` That does not agree with what you reported. – HansUp Feb 05 '16 at 17:27
  • 1
    VBA often does some implicit rounding or "close enough" comparisons to try and be helpful. For example, `?(0.2 * 5.0) = 1.0` returns `True`, but `?((0.2 * 5.0) - 1.0) = 0.0` returns `False` because `?((0.2 * 5.0) - 1.0)` returns `5.55111512312578E-17`. – Gord Thompson Feb 05 '16 at 18:35
  • HansUp, Thanks. Indeed, the formula with actual numbers does return 3.1; So my apologies. But, if a field is used and added to itself then divided by 2, the exact number is not returned unless it is a whole number. I found this is true whether the field is data type Double or Single. – random13 Feb 05 '16 at 22:08

1 Answers1

1

Both

SELECT (3.1+3.1)/2 AS Result FROM tblFoo

in a query, and

Debug.Print (3.1 + 3.1)/2

in VBA return 3.1 for me.

Are you summing up values of data type Single? They have limited precision.

Andre
  • 26,751
  • 7
  • 36
  • 80