0

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

  • Number Double
  • Number Decimal
  • Currency

Should I use the last? Or Decimal?

I wonder if this and this applies to Access?

Community
  • 1
  • 1
DBS
  • 191
  • 2
  • 14
  • 1
    With regard to the first answer you cited, Access' `Currency` type does *not* suffer the same loss of precision that plagues T-SQL's `MONEY` type. Performing the calculations from that answer in Access will produce the same (correct) results for both `Currency` and `Decimal(19,4)` fields. – Gord Thompson Aug 21 '14 at 20:27

1 Answers1

1

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.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51