Floating Point problem in MS ACCESS

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ozoCommented:
Floating point math is not exact.  What are you trying to accomplish, and how are you attempting to accomplish it?
sarabandeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
> After changing table column number from "Double" to "Single",

and why may we ask.
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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
Jeffrey CoachmanMIS LiasonCommented:
<No points wanted here either>
After changing table column number from "Double" to "Single"

and why may we ask.
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
Gustav BrockCIOCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
yep...
You learn something new every day...
;-)

Jeff
PatHartmanCommented:
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.
intoxicated_curveballAuthor 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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Changing it back won't get you there....even with a double, anything after the decimal cannot be counted on.

Jim.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.