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

• 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:

   ARTICLE-ID: Q103401
   TITLE     : ACC:How to Create Custom Domain Function Similar to DCount()

   ARTICLE-ID: Q146415
   TITLE     : ACC: Creating Functions Similar to DFirst and DLast (95/97)

   ARTICLE-ID: Q111781
   TITLE     : ACC: Rounding Errors Using Floating-Point Numbers

   ARTICLE-ID: Q35826
   TITLE     : IEEE vs. Microsoft Binary Format; Rounding Issues (Complete)