Re: SQL Sum producing rounding error on currency fields
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Tue, 12 Apr 2005 18:31:52 +0800
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---------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Andrew" <someone@xxxxxxxxxx> wrote in message
news:d3g2c4$rdu$1$8300dec7@xxxxxxxxxxxxxxxxxxx
> Thanks very much Allen, I have sent you an email containing a zipped
> database. Please reply here if you don't receive it.
>
> --
> Andrew Cutforth - AJC Software - www.ajcsoft.com
> The best folder synchronize and directory compare tool available.
> AJC Active Backup instantly archives every file you edit giving you
> unlimited undo and automatic revision control. Never lose your data
> again.
>
>
.
- References:
- SQL Sum producing rounding error on currency fields
- From: Andrew
- Re: SQL Sum producing rounding error on currency fields
- From: Allen Browne
- Re: SQL Sum producing rounding error on currency fields
- From: Andrew
- Re: SQL Sum producing rounding error on currency fields
- From: Allen Browne
- Re: SQL Sum producing rounding error on currency fields
- From: Andrew
- SQL Sum producing rounding error on currency fields
- Prev by Date: Update Date with Null value
- Next by Date: query simple sum
- Previous by thread: Re: SQL Sum producing rounding error on currency fields
- Next by thread: I need help w an Update Query
- Index(es):
Relevant Pages
|
