The Single data type is dangerous

[ChipR]

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

    Dim single1 As Single
    Dim single2 As Single
    Dim single3 As Single
 
    single1 = 425000
    single2 = 352922.2
    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

[ChipR]

Here's a question: Is DSUM safe for totaling a bunch of large dollar amounts including change?

[Stewart Ross]

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

[ChipR]

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?

[Stewart Ross]

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

[mrdav1e]

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.

[NeoPa]

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.