CODEWITHSUNDEEP
ms-accessdatabase-design
DBS
DBS

Reputation: 301

Should I use 'Currency' data type in access?

For storing monetary values in MS Access I have 3 options:

Should I use the last? Or Decimal?

I wonder if this and this applies to Access?

Upvotes: 0

Views: 2536

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49039

You don’t want to use double, since Excel or Access when using such floating numbers will cause all kinds of rounding errors.

This code demonstrates this issue rather well:

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 output 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 addition..already rounding is occurring.

One of the first lessons in computing science is that computers do NOT accurately store floating point numbers (they are only approximate). And thus one REALLY needs to avoid using real numbers when working on financial applications that involve money. Note that the above code runs the SAME even in Excel.

The lesson here is that you thus when using applications that involve money, you need to use integer values to avoid rounding. Currency is such a data type (it is an integer value scaled to include decimal places).

I have a article here that thus explains how to handle numbers that don't cause rounding errors in Access: http://www.kallal.ca/Articles/rounding/Rounding.html

The short story and rule is simply use currency data types, since floating numbers will cause you many problems.

Upvotes: 1

Related Questions