none
Adding 2 floating point numbers in query produces wrong result RRS feed

  • Question

  • I have a query tha tads 2 FP (single 2) numbers (0.0 and 89.4) and the result is 89.4000.

    see query below

    see results below

    • Edited by Steve Staab Tuesday, April 26, 2016 3:27 PM
    Monday, April 25, 2016 11:45 PM

All replies

  • What were you expecting?

    What are the data types of the fields involved?


    -Tom. Microsoft Access MVP

    Tuesday, April 26, 2016 2:03 AM
  • Please look at the OP again.  I have added the results for the query which I thought were in the post to begin with.

    I expected the results of 89.4 + 0.0 to be 89.4, not 89.400001528789.

    Tuesday, April 26, 2016 3:34 PM
  • Please look at the OP again.  I have added the results for the query which I thought were in the post to begin with.

    I expected the results of 89.4 + 0.0 to be 89.4, not 89.400001528789.

    Hi Steve,

    It is the natural inaccuracy of (almost) any floating point number. In the Single datatype it is more prominent than in the Double.

    You can get rid of it by rounding to a certain accuracy (e.g. 10 exp(-6), or convert it to the Currency datatype with a build in accuracy of 4 decimals.

    Imb.

    Tuesday, April 26, 2016 3:49 PM
  • Hi Steve,

    For this issue, I think you could set the Format as Fixed and Decimal Places as 2 in the property Sheet for Column C.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, April 27, 2016 2:45 AM
  • (you added the pictures after I responded, and modified the observed result)

    Thank you for answering one of my questions. How about the other one about the data types of the fields involved?

    Edward is right of course: you cannot expect accurate results of floating point values in a binary computer system.


    -Tom. Microsoft Access MVP


    Wednesday, April 27, 2016 4:19 AM
  • P2Score is the same

    Wednesday, April 27, 2016 12:47 PM
  • OK, that confirms it.

    One article describing what's going on is here: http://www.fmsinc.com/tpapers/math/index.html


    -Tom. Microsoft Access MVP

    Wednesday, April 27, 2016 1:40 PM
  • P2Score is the same

    Hi Steve,

    You need to set Format and Decimal Places for X or C Field in your query.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, April 28, 2016 6:04 AM
  • I think it is in bad taste for a moderator to propose their own answer as The answer. I would prefer to leave that up to others, hopefully the OP.

    On the substance of the proposed answer: I don't think this is the best advice possible. My reasons include:
    * The OP seems to ask for an explanation. As such a reply suggesting a workaround can never be the best answer if there are other posts that provide the explanation.
    * Support for the Decimal data type is very limited in VBA.
    * Suggesting use of the Format property only hides the problem. That may be OK for specific use cases, but does not address the underlying issue.
    * The proposed solution does not consider alternatives such as the use of the Currency data type, or the Round function.

    Respectfully,


    -Tom. Microsoft Access MVP

    Friday, April 29, 2016 2:38 AM
  • Edward is right of course: you cannot expect accurate results of floating point values in a binary computer system.

    -Tom. Microsoft Access MVP


    If I might propose a nuance to this: it IS possible to get accurate results from floating point computations on a binary computer system. You have to know what you’re doing to get them, however.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Sunday, May 1, 2016 7:11 PM
  • OK Peter, if you know how to get accurate results with floating point numbers please share that inof.  That would be THE ANSWER for my original post.
    Tuesday, May 3, 2016 2:43 AM
  • Hi Steve,

    Do you mean my and Tom's suggestion not work for you?

    Could you share us how you tried and what result you got?

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, May 3, 2016 5:26 AM
  • Steve –

    The info you need is contained in a good Numerical Methods text. Google that, and study for a few years.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, May 3, 2016 11:28 AM
  • I explain this issue and how to deal + solve Access rounding here:

    http://www.kallal.ca/Articles/rounding/Rounding.html

    The first day in my computing science class, we learned that binary computers don’t represent floating numbers very well at all. So for any business software the solution is to thus use integer math. So financial data, that means a “scaled” integer that does not suffer from rounding issues.

    The best bet here is to thus use a currency type column which will eliminate most rounding issues.

    My short article above on Access rounding should help you.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Sunday, May 8, 2016 5:59 PM