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

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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
0
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
0
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
0
Jeffrey CoachmanMIS LiasonCommented:
yep...
You learn something new every day...
;-)

Jeff
0
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.
0
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.
0
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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.