intoxicated_curveball
asked on
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.
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.
ozo
Floating point math is not exact. What are you trying to accomplish, and how are you attempting to accomplish it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
<No points wanted here either>
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
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
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
/gustav
yep...
You learn something new every day...
;-)
Jeff
You learn something new every day...
;-)
Jeff
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.
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.
ASKER
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.
Changing it back won't get you there....even with a double, anything after the decimal cannot be counted on.
Jim.
Jim.