SQL Sum producing rounding error on currency fields

[Andrew]

I am using Access 97 and using SQL to add up a column which is a currency
field.
The answer should be 973105676.10 but it is coming out as 973105676.0999

This is almost as if the Sum command is doing its calculation using floating
point but the field is defined in the table as a currency field !

This is an example of the SQL I am using:
SELECT Sum(myfield) AS summyfield from mytable

It is adding many thousands of records but I have checked all of these and
there are *definitely* no more than 2 digits after the decimal point on all
of these records. So the rounding error must be in the calculation.

Does anyone know what is wrong here ?

Thanks

 

[Allen Browne]

The currency data type always stores 4 decimal places of precision, even
though it typically only displays 2.

Open your table in design view.
Select the currency field.
In the lower pane, set the Format property like this:
$#,##0.0000;-$#,##0.0000
Save the table, and test the results.
You will see that there is at least one record that has values into the 3rd
or 4th decimal place.

If that does not sort out the problem, try:
SELECT CCur(Nz(Sum(myfield),0)) AS summyfield from mytable;
Explanation:
http://allenbrowne.com/ser-45.html

 

[Andrew]

Thanks for the advice but the data being added is definitely no more than 2
decimal places. I know this because I have extracted the data with only
2dp, wiped the table and re-imported.

I have also done tests like this to find the problem:
select field from table where (int(field * 100))/100 <> field

The above should find anything with more than 2dp.

I find it hard to believe that adding a currency field is causing rounding
errors. If it were using floating point internally or dividing etc I would
understand but this is currency field designed for this purpose ??
I have been using Access for years but never come across this addition
problem before and I write accounting systems.

Obviously I can fix the result by rounding but I find this scary. If the
internal maths is wrong is it possible it will go out by more than a penny ?

I have extracted the problem data into a small database the replicates the
problem. Its only just over 2MB so if anyone would like to look at it they
are welcome.

Thanks

 

[Allen Browne]

Okay: I'll bite. Email the address shown in the sig below. (Don't just reply
as that is munged.)

Andrew, what version of Access and JET are you using?
Locate msaccess.exe (typically in c:\program files\microsoft office\office).
Right-click and choose Properties. What is the full version number (major
and minor)?

Also msjet40.dll (typically in windows\system32)?

 

[Andrew]

Thanks very much Allen, I have sent you an email containing a zipped
database. Please reply here if you don't receive it.

 

[Allen Browne]

Hi Andrew

After a quick look at your file, I can confirm the behaviour you describe. I
can only assume that what you have unearthed is a flaw with the JET 3.5x
engine, though I am at a loss to know how they could possibly get
fixed-point math wrong.

1. The data is stored correctly.
Programmatically looping through the values using the function below yields
the correct values. This confirms that the data is being stored correctly,
and that the fault is with the query engine.

2. The data works correctly in Access 2003.
I tried creating a new database in A2000 format, importing the data, and
running a similar query. The new database did NOT show the .0999 result,
i.e. it behaved correctly.

Access 2000 and later use JET 4 - a different query engine. Again, this
confirms the fault is with the JET 3.5 engine used by Access 97.

3. Just in case it was the conversion modifying the actual stored values, I
then converted this well-behaved database back to A97 again. The A97 version
DID display the problem.

This leaves me no doubt that it is the JET 3.5 engine that is at fault.

It is just remotely possible that the engine is being used differently under
Win2000/XP than it was when originally designed under Win9x. However I don't
have a Win9x setup here to test this idea, and it seems unlikely (even
though there are some problems that occur only when A97 is run on WinXP.)

FYI, my testing was on WinXP SP2, using msaccess version 8.0.0.5903, and
msjet35.dll version 3.51.3328.0. (The msjet40.dll version is not a factor in
Access 97.)

Not sure that helps much, but you have absolutely zero chance of getting
Microsoft to fix anything in JET 3.5. I can't even get them to fix the
problems in JET 4. Hopefully it helps to know that it is not anything you
are doing wrong or have any control over.

---------code begins---------
Function FindSmall() As Currency
Dim rs As DAO.Recordset
Dim strSql As String
Dim curTotal As Currency

strSql = "SELECT mwiptim.* FROM mwiptim;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
With rs
Do While Not .EOF
curTotal = curTotal + !mwiptim_time_value
.MoveNext
Loop
End With
rs.Close

Set rs = Nothing
Debug.Print Format(curTotal, "#,##0.0000")
FindSmall = curTotal
End Function
---------code ends---------