Currency or Double for currency values

[Tim Zych]

I have a table here that will store currency values. I've been reading up on
the differences between the datatypes available and there seem to be 2
viable candidates: Currency and Double.

Double can hold bigger/smaller numbers than currency, but suffers from the
floating point number problem.
Currency doesn't have the floating point problem that double has.

(Please correct me if I'm missing something here).

Being that, is there another factor I need to take into consideration? I'm
leaning toward Currency and don't anticipate storing > than the max of 922
trillion dollars.

Or, should I just use double and not worry about the floating point issue?

Thanks for enlightenment.

 

[Allen Browne]

Use Currency.

If you have calculations (such as tax), you can round the result of the
calculation to 2 places, and then when you add up all the invoices (or
whatever) the results will be exactly right.

 

[Randy]

I have a table here that will store currency values. I've been reading up
on
the differences between the datatypes available and there seem to be 2
viable candidates: Currency and Double.

Double can hold bigger/smaller numbers than currency, but suffers from the
floating point number problem.
Currency doesn't have the floating point problem that double has.

(Please correct me if I'm missing something here).

Being that, is there another factor I need to take into consideration? I'm
leaning toward Currency and don't anticipate storing > than the max of 922
trillion dollars.

Or, should I just use double and not worry about the floating point issue?

Thanks for enlightenment.

Tim, MS Access was designed to high perform on Currency data types. My
advice is not to replace the data type for the sake of having more
precision. That will affect Access calculation speeds, specially on large
data sets and several queries.

-Randy

 

[Jamie Collins]

I have a table here that will store currency values. I've been reading up on
the differences between the datatypes available and there seem to be 2
viable candidates: Currency and Double.

There is a third: DECIMAL.

Double can hold bigger/smaller numbers than currency, but suffers from the
floating point number problem.
Currency doesn't have the floating point problem that double has.
should I just use double and not worry about the floating point issue?

Definitely not DOUBLE. Yes, you should worry about floating point
errors.

Being that, is there another factor I need to take into consideration

Yes, proprietary features i.e. non-standard implementation.

DECIMAL is part of the SQL-92 standard.

CURRENCY is proprietary to MS Jet. It is different even to its closest
relative in the MS stable, MS SQL Server's MONEY.

Normally you'd worry that the implementation could change in the next
product release. This has happened in the past e.g.

Changes with DISTINCTROW in Microsoft Access 97
http://support.microsoft.com/default.aspx?scid=kb;en-us;168438
"Changes in the Microsoft Jet database engine (Jet 3.5) cause the
program to handle the SQL reserved word, DISTINCTROW, differently than
it did in earlier versions."

However, a future version of Jet now seems unlikely.

Another consideration with proprietary features is portability. One
day you may move on from MS Access/Jet to a more capable DBMS and
using standard SQL data types makes the process easier.

Jamie.

--

 

[Tim Zych]

Thanks for the replies everybody!

 

[Joe Williams]

Can anyone enlighten me as to the "floating point problem"? I use double all
the time. Thanks

- joe

 

[Allen Browne]

Joe, it's not really a problem. The Double is often useful.

Many real number cannot be represented in binary with perfect precision, so
the Double has a limit of about 15 decimal digits.

In most cases the impresision is not crucial. Try this example, and you will
see that after 7 passes through the loop, it still keeps going, because the
result is not exactly 1 after you add 1/7 (as a double) seven times:

Sub ShowRoundingError()
'Note: Use Ctrl+Break to stop this loop.
Dim dblValue As Double
Dim dblResult As Double

dblValue = 1 / 7
Do Until dblResult = 1 'Does not terminate!!!
dblResult = dblResult + dblValue
Debug.Print dblResult
Loop
End Sub

 

[John Vinson]

Can anyone enlighten me as to the "floating point problem"? I use double all
the time. Thanks

As Allen says, the problem is that a Double is *an approximation*.
Just as 1/7 or 1/3 cannot be represented exactly as a decimal fraction
(both are infinite repeats, .142856142856142856... and .3333...
respectively), so many numbers cannot be represented exactly using the
binary fractions in Float or Doubles.

Among the numbers that cannot be represented are such values as 0.1 or
0.01 - and these (surprise!) turn up pretty often in currency data!

Any calculations done with Doubles will be accurate to some 14 decimal
places (24 bits), which is fine for most purposes. Where you will run
into trouble is *testing equality* - depending on how you arrive at
the value, what *LOOKS* like 1.74 in two different tables might be
1.739999999999981 in one and 1.74000000000003 in another. Attempting
to join these two tables on such a field, or even use a query
criterion, will miss data.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Jamie Collins]

Joe, it's not really a problem. The Double is often useful.

Allen, May I respectfully remind you of the title of this thread. If
you'd had experience of writing software for financial transactions
with international bodies such as the European Union, you'd know why
using an inexact data type such as Double for monetary values is
unacceptable, sometimes criminal.

Jamie.

--