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.
RESOLUTIONCreating 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.
- Open the sample database Northwind.mdb, and import the module you
created in step 1 of "Create the Function."
- Press CTRL+G to open the Debug window.
- 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 INFORMATIONSteps to Reproduce Behavior
- Start Microsoft Access and open any database.
- Create the following table and save it as tblCurrencySum:
Table: tblCurrencySum
------------------------
Field Name: CurrencyTest
Data Type: Currency
- 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)
- Press CTRL+G to open the Debug window.
- 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
|