Data is different than in database

[fniles]

I am using MS Access and VB .NET 2005 (the same problem happens in VB.NET
2008).
I am reading from a query using DataReader (the same problem happens if I
use OLEDBDataAdapter).
When I run the query in Access, the value for Cash = 830.004999999999, SC =
1692.5 and Profit = -862.495000000001.
Cash = SC + Profit.
But when I read the value from the program, this is what I got:
Cash = 830.000000000489
SC = 1692.5
Profit = -862.499999999511.
Why this difference and how can I fix it ?
Thank you.

Here are the codes:
Dim m_cmd As OleDb.OleDbCommand
Dim m_dr As OleDb.OleDbDataReader
Dim sSQL As String
m_cmd = New OleDb.OleDbCommand
With m_cmd
.Connection = adoConOLE
.CommandText = "select Cash,Profit from myQuery where Account =
'123'"
End With
m_dr = m_cmd.ExecuteReader()
If m_dr.Read Then
sSQL = sSQL & " " & m_dr.Item("Cash") --> this returns
830.000000000489 instead of 830.004999999999 when I run it from Access
sSQL = sSQL & " " & m_dr.Item("SC") --> this returns 1692.5,
which is the same as when I run it on Access
sSQL = sSQL & " " & m_dr.Item("Profit") --> this
returns -862.499999999511 instead of -862.495000000001 when I run it from
Access
End If

 

[Cowboy \(Gregory A. Beamer\)]

floating point numbers are notorious for being off. This can happen even
when you do not use two different programs (.NET versus Access).

Not sure why things are so far off, however. What is the data type in
Access?

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss

or just read it:
http://gregorybeamer.spaces.live.com/

*************************************************
| Think outside the box!
|
*************************************************

 

[fniles]

The query is very complicated, it refers to another query, who refers to
another query, who refers to another query.
But the original table's column where Profit comes from is of type "text"
field of 50 character length (I know, I know, why is it a text field ? It
was like that from the beginning, and I changed it when we convert to SQL
Server, but the original Access database is a text field .... )
Is there anything that I can do to fix this data discrepancy issue ?
BTW, this problem does not happen in a VB6 program

Thank you.

 

[Cowboy \(Gregory A. Beamer\)]

If this were merely a pure rounding issue, you can set up comparison to only
look at so many points to the right of the decimal, but there is something
more insidious here. And I am not sure, right now, how to solve it.

Not sure why it does not happen in VB6, but COM does handle things a bit
differently. Somewhere along the way, there is a conversion to floating
point. That is where I would look for the solution. Since Access is COM
based, you cannot easily debug this one, which would be my other suggestion.
There is likely a flip in one of the queries.

And, I am not going to beat you up for it being a text field. We all carry
garbage that we shouldn't have to as we work on applications.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss

or just read it:
http://gregorybeamer.spaces.live.com/

*************************************************
| Think outside the box!
|
*************************************************