Microsoft KB Archive/177360

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

Article ID: 177360

Article Last Modified on 1/22/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q177360



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



SYMPTOMS
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.



Creating the Function
To create a custom domain function that sums the data in a field, follow these steps:

  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  Compile and save the module. 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.



Steps to Reproduce Behavior
 Start Microsoft Access and open any database.</li>  Create the following table and save it as tblCurrencySum:

<pre class="fixed_text">     Table: tblCurrencySum Field Name: CurrencyTest Data Type: Currency </li>  Open the tblCurrencySum table and enter two records that contain the following data in the CurrencyTest field:

<pre class="fixed_text">     $123,456,789,012,345.67 ($123,456,789,012,345.66)                       </li> Press CTRL+G to open the Debug window.</li> 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.</li></ol>

<div class="references_section">