By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,407 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

The Single data type is dangerous

Expert 100+
P: 1,287
I'm using Access 2007 on Windows XP, and I just noticed a total on one of my forms was wrong. It came down to
Expand|Select|Wrap|Line Numbers
  1.     Dim single1 As Single
  2.     Dim single2 As Single
  3.     Dim single3 As Single
  4.  
  5.     single1 = 425000
  6.     single2 = 352922.2
  7.     single3 = single1 - single2
Guess what single3 equals. 72077.81

Not good for my dollar amount calculations! I did find that the Double type and Currency type got it right, but I'm pretty sure I'll never use Single again.

Obviously this is due to the inaccuracy of representing decimal numbers in binary, but the range of Single would really suggest that you could do math with reasonable size numbers and expect accurate results.

It's amazing to me that in Single math 1,000,000.1 - 1,000,000 = .125
Feb 26 '09 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 1,287
Here's a question: Is DSUM safe for totaling a bunch of large dollar amounts including change?
Feb 26 '09 #2

Expert Mod 2.5K+
P: 2,545
Hi Chip. Problem is that single precision values have only 7 significant digits of precision - and if you look at the examples you give you are using all of them, which is why the calculations are giving very inexact results.

Rounding issues will always arise in any floating-point number system, but single-precision values have the least number of significant digits, so rounding problems will be a particular issue if the number of significant digits is larger than the type can actually manipulate.

You could either use double-precision to improve this (15 significant digits), or a variant typecast to the decimal type using the CDec() function (which has the most digits of precision of all VB types, 28 decimal places).

I never use single-precision values in any calculations; doubles generally work well for most things, but even so rounding errors need to be taken into account when comparing values - strict equality of floating point numbers is unachievable given the small round-off errors that are inevitable in such number systems.

See the following articles for further technical guidance:

http://msdn.microsoft.com/en-us/libr...le(VS.85).aspx
http://msdn.microsoft.com/en-us/libr...le(VS.85).aspx
http://msdn.microsoft.com/en-us/libr...m.decimal.aspx

-Stewart
Feb 26 '09 #3

Expert 100+
P: 1,287
Thanks, Stewart. I wasn't aware of the Decimal type. I couldn't find those same specifications with information about the Currency type. Do you know if Currency is safe, or should I just use Decimal for my calculations?
Feb 26 '09 #4

Expert Mod 2.5K+
P: 2,545
Hi Chip. The Currency type is not only safe, it is exact within its 4-decimal place limits - it is not a floating point type at all, but a whole-number type (the currency value multiplied by 10,000 for storage - hence the 4 decimal place limitation).

The short MSDN article on the type is here (and very similar to the Access help entry):

http://msdn.microsoft.com/en-us/library/aa458706.aspx

and a better MS article here:
http://support.microsoft.com/kb/51414

-Stewart
Feb 26 '09 #5

P: 1
All Floating Point is dangerous, single and double. Decimal type is a step in the right direction but the problem is, at least how I see it, is the more numbers to the left of the decimal, the less numbers on the right. I do not like this.
Aug 22 '11 #6

NeoPa
Expert Mod 15k+
P: 31,418
Stewart has made clear that the misuse of the Single data type is dangerous. The correct use of it is fine. The Access Help system includes such warnings and doesn't recommend the use of floating-point number types for storing currency values.
Aug 23 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.