ACC: DSum Causes Rounding Error on Large Currency Fields (95/97)

ID: Q177360


The information in this article applies to:
  • Microsoft Access versions 7.0, 97


SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

Using the domain aggregate (totals) function DSum() to total a currency field may cause rounding errors for values exceeding fourteen significant digits. The same behavior occurs when you use the aggregate Sum() function.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.


CAUSE

If you use the Currency data type, rounding errors typically do not occur because Currency values are scaled integers rather than floating point numbers. However, the Sum() and DSum() functions store an accumulated total as a floating point number, which is limited to approximately 14 significant digits. If you require all 19 digits of precision (15 to the left of the decimal point and 4 to the right of the decimal point), you can use a custom domain aggregate function. You should use the built-in Sum() and DSum() functions if you do not require this degree of precision.


RESOLUTION

Creating the Function

To create a custom domain function that sums the data in a field, follow these steps:
  1. Create a new module and enter the following code:
    
          Function DCurSum(Expr As String, Domain As String, _
                Optional Criteria As Variant) As Variant
    
             On Error GoTo Err_DCurSum
    
             Dim rst As Recordset, curTotal As Currency, fld As Field
             Dim db As Database, strSQL As String
             Set db = CurrentDb
             If IsMissing(Criteria) Then  ' No criteria provided.
                strSQL = "Select " & Expr & " AS DCurSumExpr From " & _
                   Domain & ";"
             Else  ' Add criteria to SQL statement.
                strSQL = "Select " & Expr & " AS DCurSumExpr From " & _
                   Domain & " WHERE " & Criteria & ";"
             End If
             Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
             curTotal = 0
             Do Until rst.EOF   ' Loop through all the records.
                If IsNumeric(rst("DCurSumExpr")) Then _
                   curTotal = curTotal + rst("DCurSumExpr") ' Add the numbers.
                rst.MoveNext
             Loop
             DCurSum = CCur(curTotal)
    
          Exit_DCurSum:
             Exit Function
          Err_DCurSum:
             DCurSum = "#Error"
             Resume Exit_DCurSum
          End Function 


  2. Compile and save the module.


  3. To use the custom domain function, replace DSum() with DCurSum(). For example, in the control source of a text box, type

    =DCurSum("[YourCurrencyField]", "[YourTable]")

    where YourCurrencyField is the name of the field you want to sum and YourTable is the name of the table or query that contains the currency field.


Using DCurSum() with Criteria

You can use criteria with DCurSum() in the same way you use criteria with DSum(). The following example uses DCurSum() to calculate the value of the entire inventory of products whose CategoryID is 1.
  1. Open the sample database Northwind.mdb, and import the module you created in step 1 of "Create the Function."


  2. Press CTRL+G to open the Debug window.


  3. Type the following line in the Debug window, and then press ENTER:

    ?DCurSum("UnitPrice * UnitsInStock","Products","CategoryID=1")

    Note that this expression returns the total value of the inventory of products whose CategoryID is 1 to the Debug window.

    Note also that the first argument of the DCurSum() function can be either a field name or a calculation. However, you must ensure that the expression in the first argument returns a monetary value; otherwise, the DCurSum() function returns a value of 0; the function tests a value to see if it is numeric before adding it to the total.



MORE INFORMATION

Steps to Reproduce Behavior


  1. Start Microsoft Access and open any database.


  2. Create the following table and save it as tblCurrencySum:
    
          Table: tblCurrencySum
          ------------------------
          Field Name: CurrencyTest
             Data Type: Currency 


  3. Open the tblCurrencySum table and enter two records that contain the following data in the CurrencyTest field:
    
          $123,456,789,012,345.67
         ($123,456,789,012,345.66) 


  4. Press CTRL+G to open the Debug window.


  5. Type the following in the Debug window, and then press ENTER:

    ?DSum("[CurrencyTest]","[tblCurrencySum]")

    Note that this expression returns a value of 0.0156 to the Debug window. If you use this expression in a control or field that is formatted as Currency, the result appears as $0.02. The expected result is $0.01.



REFERENCES

For more information about custom domain functions, please see the following articles in the Microsoft Knowledge Base:

Q103401 ACC:How to Create Custom Domain Function Similar to DCount()

Q146415 ACC: Creating Functions Similar to DFirst and DLast (95/97)

For more information about rounding errors, please see the following articles in the Microsoft Knowledge Base:

Q111781 ACC: Rounding Errors Using Floating-Point Numbers

Q35826 IEEE vs. Microsoft Binary Format; Rounding Issues (Complete)

Additional query words: prb incorrect wrong rounding rounded incorrectly off

Keywords : SynFnc
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbprb


Last Reviewed: April 26, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.