Adding 2 floating point numbers in query produces wrong result

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

Answer 1

What were you expecting?

What are the data types of the fields involved?

---

-Tom. Microsoft Access MVP

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

(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

Answer 6

P2Score is the same

Answer 7

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

Answer 8

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.

Answer 9

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

Answer 10

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

Answer 11

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.

Answer 12

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.

Answer 13

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

Answer 14

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