We help IT Professionals succeed at work.

Floating Point problem in MS ACCESS

on
After changing table column number from "Double" to "Single", there is now a problem when I query that column...

Instead of a number showing as 1.01

It shows as 1.00999999046326

And trying to set the decimal plays or using ROUND() doesn't work.
Comment
Watch Question

View Solutions Only

Most Valuable Expert 2014
Top Expert 2015

Commented:
Floating point math is not exact.  What are you trying to accomplish, and how are you attempting to accomplish it?
Top Expert 2016
Commented:
the problem occurs because you changed the format from double to single. the stored values are double and after conversion from double to single they were not properly rounded (what actually is a bug).

you may try to change back to double, then check if it is ok, then change to text, check if it is still ok, and then change to double. if that doesn't work, I would go back to double, export the data to a csv, then delete all data (make a backup before), change from double to single and import back from csv.

Sara
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
> After changing table column number from "Double" to "Single",

However, also with Double you may encounter the same issue with other values.

If you don't store values with more than four decimals, the solution is to use data type Currency. This will never produce errors like those you have seen.

/gustav
President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
Just to tack on a bit; the double and single data types both use floating point math.  That means the decimal portion is not precise and you cannot count on it.

The currency data type is what is called a scaled integer; a number is multiplied by a power of 10, stored in a single or double, and when extracted, the power of 10 is divided back out.   Currency is 4 decimals, so x * 10^4  and something like:

12.3456 becomes 123456.0 and now can be stored in a double or single with no loss of precision.

With the decimal data type, which is also a scaled integer,  you can set the number of decimals of precision that you want.  However the more you ask for, the more you reduce the maximum value that can be stored.

Currency is often a very good choice

Jim.
MIS Liason
Most Valuable Expert 2012

Commented:
<No points wanted here either>
After changing table column number from "Double" to "Single"

lol, ...yep, unless you are really having an issue with the "Storage size" of double being greater that that of a single (8 bytes vs 4 bytes), then I would leave it at double, (especially if you were not having any issue with Double...)

BTW, the Experts above have also taught me something...
I never considered using Currency to avoid decimal issues, ...besides Currency has an 8 byte storage size (the same as double)...
- every day is a school day.
;-)

JeffCoachman
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Ha ha. Perhaps you have refrained from using Currency because you believed it could only hold Dollar amounts ... as I someone mentioned a long time ago - can't recall where. Great fun, except that I think we all learned the limitations of floating number the hard way.

/gustav
MIS Liason
Most Valuable Expert 2012

Commented:
yep...
You learn something new every day...
;-)

Jeff
Distinguished Expert 2017

Commented:
Here's an interesting article by Luke Chung of FMS that explains the problem.

http://www.fmsinc.com/TPapers/math/index.html

I switched to Currency years ago and never use anything else unless I need more than four decimal digits.  Don't confuse the Currency data type with the Currency format.  The currency data type can be formatted as money, percent, etc.

Commented:
Thanks all. I changed to Single as I was trying to optimize/improve performance (can't say it really did). I'll just try changing it back.
President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
Changing it back won't get you there....even with a double, anything after the decimal cannot be counted on.

Jim.