Why the fuzzy math in Access?

[Guest]

Hello:

I have a couple mathematical expressions built into my database (in forms
and in queries) and I notice that I get some strange results sometimes. I'll
give a couple rows from a query which typifies my question:

Var1 Var2 Var3: [Var1] - [Var2]
0.8 1 -0.199999988079071
0.8 0.9 -9.99999642372131E-02
1.1 1.2 -0.100000023841858

Obviously, Var3 for the above three rows ought to be exactly -0.2, -0.1, and
-0.1. In my other examples, I get similar results where it is 'real' close to
the correct answer, but just off by some miniscule amount. I get the same
results with another expression (on a form) where it is addition instead of
subraction.

Of note, Var1 and Var2 in this example are both being stored as a Single
since I need them to be decimals.

Very weird. What could I be doing wrong?

Thanks!

 

[Albert D. Kallal]

One of the first courses you get in a computing class is abut how computers
actually can't represent *exact* floating point numbers.

Thus, when a computer stores 1/10 (one tenth), the floating point
representation is only approximate.

Note the following simple code:

Public Sub TestAdd()


Dim MyNumber As Single
Dim i As Integer

For i = 1 To 10
MyNumber = MyNumber + 1.01
Debug.Print MyNumber
Next i
End Sub

Here is the actual outpput of the above:

1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1

You can see that after just 7 additions..already rounding is occurring

And if we add the following line of code to the end of the above:


if MyNumber = 10.1 = True then
msgbox "the number is 10.1"
else
msgbox "the number is somthing else"
endif


The above will actual produce:

the number is something else

So, a basic understanding how computers represent real numbers comes as a
surprise to most.

The solution in most cases is to use scaled integers. In fact, if you write
ANY accounting type software, you not have any success using real
numbers...you have to use whole numbers (integers).

Fortunately, for financial applications, you can use the built-in currency
format (in access, currency data type is a scaled integer with up to 4
decimal places). If you need MORE then 4 decimal places, then you have to
scale the numbers yourself, or use a diffent data type.

In your examples, since you don't seem to need more then 2 decimal places,
then you likely can use currency.

You can also use what is called packed decimal type. It not directly
supported in ms-access vba code as a data type (you just use variant in this
case). However, this format is new to jet 4.0 and later (that means
ms-access 2000 and later). The field type is called decimal, and it good for
28 significant digits. You note when you choose decimal in the table design,
you see the "scale" setting, and that just a fancy term for saying how many
decimal places we going to use, or "scale" form those possible 28 max digits
we have.

I would suggest that you start out using currency type since it also
directly supported in code your write.

However, if you need more then 4 digits, then "scaling" in your code could
be eliminated by choose decimal data type.

So, welcome to the world of computers where real numbers are not able to be
represented as real numbers in a binary computer!

As mentioned, if you writing software for business with financial
information, then you need to keep the above lessons in mind or you not be
able to balance anything!!

 

[Guest]

Wow, that's absolutely amazing actually (the fact that this is almost
expected). I have to laugh at how computing is thought to be so exact, but
there is this subtle but significant flaw in what I thought was a very simple
process.

Thanks so much for explaining all this. I think that the currency format
will work fine for what I am doing.

Thanks!

 

[Tony Toews [MVP]]

Wow, that's absolutely amazing actually (the fact that this is almost
expected). I have to laugh at how computing is thought to be so exact, but
there is this subtle but significant flaw in what I thought was a very simple
process.

Yeah, it is quite funny isn't it.

Thanks so much for explaining all this. I think that the currency format
will work fine for what I am doing.

And that is exactly why the Currency type was created. However it
has four decimal places. I would strongly urge you, when doing any
multiplying or dividing such as with tax percentage, to round off to
two decimal places. (Assuming that's appropriate for your currency.)
Otherwise when you run reports you will see numbers that look quite
reasonable but the grand total is out by a few pennies.

TOny
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/