Re: Why the fuzzy math in Access?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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!

"Albert D. Kallal" wrote:

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!!


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx



.

Relevant Pages

  • Re: Anyone had different results between Access calculations and E
    ... Thanks, Albert. ... Dim MyNumber As Single ... If that is the case, then use a currency field, NOT ... because we have a native built in currency data type. ...
    (microsoft.public.access.reports)
  • Re: How to overcome very slight calculation error.
    ... Dim MyNumber As Single ... you can't store, or nor use "real" numbers, as they are a approximates. ... solution in this case is to use decimal number, or a data type of currency. ...
    (microsoft.public.access.modulesdaovba)
  • Re: How to "POST" accounting data into underlying tables
    ... I'll switch the Data Type to ... I have a Receipts Allocation table in which the payment received for invoices ... caused by the use of the word "currency" in two ... Whether you select Currency or Number Data Type, amongst the Format ...
    (microsoft.public.access.macros)
  • Re: Rounding info needed-A2k
    ... But, instead of saving the entered amount -1353.57, it's stores ... accurate out to 82 million digits ... I chose the number/double type over the currency because I didn't like the ... you have a floating point data type. ...
    (comp.databases.ms-access)
  • Re: Currency Datatype Really Good to Use For Non-currency Fields?
    ... floating point types ... of type CURRENCY, plenty of scope for confusion there, I think. ... For a sort to 'fail' the resultset must contain negative values AND the ... The CHARdata type ...
    (microsoft.public.access.tablesdbdesign)