If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Currency or Double for currency values
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. |
#2
|
|||
|
|||
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Tim Zych" wrote in message ... 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. |
#3
|
|||
|
|||
Tim Zych wrote:
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 |
#4
|
|||
|
|||
"Tim Zych" wrote in ...
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...b;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. -- |
#5
|
|||
|
|||
Thanks for the replies everybody!
"Tim Zych" wrote in message ... 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. |
#6
|
|||
|
|||
Can anyone enlighten me as to the "floating point problem"? I use double all
the time. Thanks - joe "Tim Zych" wrote in message ... Thanks for the replies everybody! "Tim Zych" wrote in message ... 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. |
#7
|
|||
|
|||
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 -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Joe Williams" wrote in message ... Can anyone enlighten me as to the "floating point problem"? I use double all the time. Thanks - joe "Tim Zych" wrote in message ... Thanks for the replies everybody! "Tim Zych" wrote in message ... 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. |
#8
|
|||
|
|||
On Tue, 9 Nov 2004 15:13:41 -0500, "Joe Williams"
wrote: 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 |
#9
|
|||
|
|||
"Allen Browne" wrote ...
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. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Currency values set to 2 decimal places | clappus | General Discussion | 1 | November 5th, 2004 07:21 PM |
need to calculate intermediate values | Chris Alexander | Worksheet Functions | 3 | August 18th, 2004 02:14 PM |
Howto: Filling in values based upon a particular cell | Eric Tubbs | Worksheet Functions | 6 | December 3rd, 2003 07:24 PM |
Using Datetime values as Category | Dave Shea | Charts and Charting | 1 | November 29th, 2003 04:58 AM |
Calculating currency values | DMac | Worksheet Functions | 1 | November 6th, 2003 09:09 AM |