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 OLEDBDataAdapte r).
When I run the query in Access, the value for Cash = 830.00499999999 9, SC =
1692.5 and Profit = -862.49500000000 1.
Cash = SC + Profit.
But when I read the value from the program, this is what I got:
Cash = 830.00000000048 9
SC = 1692.5
Profit = -862.49999999951 1.
Why this difference and how can I fix it ?
Thank you.

Here are the codes:
Dim m_cmd As OleDb.OleDbComm and
Dim m_dr As OleDb.OleDbData Reader
Dim sSQL As String
m_cmd = New OleDb.OleDbComm and
With m_cmd
.Connection = adoConOLE
.CommandText = "select Cash,Profit from myQuery where Account =
'123'"
End With
m_dr = m_cmd.ExecuteRe ader()
If m_dr.Read Then
sSQL = sSQL & " " & m_dr.Item("Cash ") --this returns
830.00000000048 9 instead of 830.00499999999 9 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("Prof it") --this
returns -862.49999999951 1 instead of -862.49500000000 1 when I run it from
Access
End If

[Cowboy \(Gregory A. Beamer\)]

Re: Data is different than in database

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" <fniles@pfmail. comwrote in message
news:%230JvyDPk IHA.5820@TK2MSF TNGP04.phx.gbl. ..

>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 OLEDBDataAdapte r).
When I run the query in Access, the value for Cash = 830.00499999999 9, SC
= 1692.5 and Profit = -862.49500000000 1.
Cash = SC + Profit.
But when I read the value from the program, this is what I got:
Cash = 830.00000000048 9
SC = 1692.5
Profit = -862.49999999951 1.
Why this difference and how can I fix it ?
Thank you.
>
Here are the codes:
Dim m_cmd As OleDb.OleDbComm and
Dim m_dr As OleDb.OleDbData Reader
Dim sSQL As String
m_cmd = New OleDb.OleDbComm and
With m_cmd
.Connection = adoConOLE
.CommandText = "select Cash,Profit from myQuery where Account =
'123'"
End With
m_dr = m_cmd.ExecuteRe ader()
If m_dr.Read Then
sSQL = sSQL & " " & m_dr.Item("Cash ") --this returns
830.00000000048 9 instead of 830.00499999999 9 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("Prof it") --this
returns -862.49999999951 1 instead of -862.49500000000 1 when I run it from
Access
End If
>
>

[fniles]

Re: Data is different than in database

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)" <NoSpamMgbworld @comcast.netNoS pamMwrote in
message news:unLQjSPkIH A.5956@TK2MSFTN GP03.phx.gbl...

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" <fniles@pfmail. comwrote in message
news:%230JvyDPk IHA.5820@TK2MSF TNGP04.phx.gbl. ..

>>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 OLEDBDataAdapte r).
>When I run the query in Access, the value for Cash = 830.00499999999 9, SC
>= 1692.5 and Profit = -862.49500000000 1.
>Cash = SC + Profit.
>But when I read the value from the program, this is what I got:
>Cash = 830.00000000048 9
>SC = 1692.5
>Profit = -862.49999999951 1.
>Why this difference and how can I fix it ?
>Thank you.
>>
>Here are the codes:
> Dim m_cmd As OleDb.OleDbComm and
> Dim m_dr As OleDb.OleDbData Reader
> Dim sSQL As String
> m_cmd = New OleDb.OleDbComm and
> With m_cmd
> .Connection = adoConOLE
> .CommandText = "select Cash,Profit from myQuery where Account
>= '123'"
> End With
> m_dr = m_cmd.ExecuteRe ader()
> If m_dr.Read Then
> sSQL = sSQL & " " & m_dr.Item("Cash ") --this returns
>830.0000000004 89 instead of 830.00499999999 9 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("Prof it") --this
>returns -862.49999999951 1 instead of -862.49500000000 1 when I run it from
>Access
> End If
>>
>>

>
>

[Cowboy \(Gregory A. Beamer\)]

Re: Data is different than in database

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!
|
*************** *************** *************** ****
"fniles" <fniles@pfmail. comwrote in message
news:eScf2BRkIH A.4684@TK2MSFTN GP06.phx.gbl...

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)" <NoSpamMgbworld @comcast.netNoS pamMwrote in
message news:unLQjSPkIH A.5956@TK2MSFTN GP03.phx.gbl...

>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" <fniles@pfmail. comwrote in message
>news:%230JvyDP kIHA.5820@TK2MS FTNGP04.phx.gbl ...

>>>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 OLEDBDataAdapte r).
>>When I run the query in Access, the value for Cash = 830.00499999999 9,
>>SC = 1692.5 and Profit = -862.49500000000 1.
>>Cash = SC + Profit.
>>But when I read the value from the program, this is what I got:
>>Cash = 830.00000000048 9
>>SC = 1692.5
>>Profit = -862.49999999951 1.
>>Why this difference and how can I fix it ?
>>Thank you.
>>>
>>Here are the codes:
>> Dim m_cmd As OleDb.OleDbComm and
>> Dim m_dr As OleDb.OleDbData Reader
>> Dim sSQL As String
>> m_cmd = New OleDb.OleDbComm and
>> With m_cmd
>> .Connection = adoConOLE
>> .CommandText = "select Cash,Profit from myQuery where Account
>>= '123'"
>> End With
>> m_dr = m_cmd.ExecuteRe ader()
>> If m_dr.Read Then
>> sSQL = sSQL & " " & m_dr.Item("Cash ") --this returns
>>830.000000000 489 instead of 830.00499999999 9 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("Prof it") --this
>>returns -862.49999999951 1 instead of -862.49500000000 1 when I run it
>>from Access
>> End If
>>>
>>>

>>
>>

>
>