Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    number and currency data type, which one? (2003)

    Is there benefit to using one or another to store money values?

    Number data type with currency format
    or
    Currency data type

    Thanks, Sarah

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: number and currency data type, which one? (2003)

    The Currency data type, as the name implies, is especially suitable for amounts of money. It is a fixed point format with 19 significant digits, of which 4 decimal places, so it can store larger amounts without losing precision than the Double precision floating point data type.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: number and currency data type, which one? (2003)

    The Single and Double data types are stored as floating point numbers, while the Currency data type is stored as an 8-byte signed integer, scaled by 10,000 (ie, scaled integer). Currency is accurate to 15 digits to the left of the decimal point, and 4 digits to the right, with a range of values as follows:

    (2 ^ 63 -1) / 10,000 = +922337203685477.5807 to
    (2 ^ 63) / 10,000 = -922337203685477.5808

    As noted in MSKB 210423, floating-point number errors occur "because decimal fractions do not always have exact binary equivalents, which can result in rounding errors." Article recommends using Monetary data types, such as Currency, that are accurate to four decimal places or fewer to avoid rounding errors seen with floating-point numbers. See article for more info:

    ACC2000: Rounding Errors When You Use Floating-Point Numbers

    Test sub that demonstrates possible floating point errors:

    <code>Public Sub TestCurrency()</code>

    <code> Dim i As Integer</code>
    <code> Dim dblSum As Double</code>
    <code> Dim curSum As Currency</code>

    <code> For i = 1 To 10000</code>
    <code> dblSum = dblSum + 0.0001</code>
    <code> Next i</code>
    <code> Debug.Print TypeName(dblSum) & ":"; dblSum</code>

    <code> For i = 1 To 10000</code>
    <code> curSum = curSum + 0.0001</code>
    <code> Next i</code>
    <code> Debug.Print TypeName(curSum) & ":"; curSum</code>

    <code>End Sub</code>

    <code>' Test results:</code>
    <code>Double: 0.999999999999906 </code>
    <code>Currency: 1</code>

    As shown, Currency might be a better choice in accounting and other applications that require accuracy in monetary calculations.

    HTH

  4. #4
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: number and currency data type, which one? (2003)

    Thanks for the explanation!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •