Why are values not seen as being the same?

[Kelvin]

I have a query with two tables.
They are linked through an ID field.
My query is looking for dollar amounts that don't match between the tables.

Odd think is, a few of the values that show up as not being equal, really
are.
I thought maybe there might be a space in one, but I can't see that there
are any differences.

I've tried this with both the tables imported, one is from SQL Server 2005
and the other is data impored from an Excel spreadsheet...

Any idea why the Access query would not see two values the same?

I'm using Access 2003.

Kelvin

 

[Jeff Boyce]

Kelvin

The way computers store "Double" and "Single" numbers is different than the
way they store "Currency" values.

You might give a value of 4.0, but the computer might store this as
3.9999999 (as a double). If you gave it 4.0 (as a Currency data type), it
would store 4.0. So, is 3.9999999 = 4.0? No!

Any chance you're working with more than one data type?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Lord Kelvan]

can you paste some sample data i do think that maybe the ones coming
from excel are text and that may be causing the problem try in your
comparision

cint(excelvalue) <> sqlvalue

but thats just a guess without seeing the results of the query and
some sample data

Regards
Kelvan

 

[Kelvin]

Thanks for you reply

I was suspect of the data types being different so I imported the table that
I had been linked to.
I brought up the properties of the field in both tables and they are
identical as best I can tell.
Double with auto decimal.
The data looks like this coming out of the query
qry_NotEqual DateOrder ReceiveRef ReceivePurId Sundry Amt AmtSubtotal Amt
Due
4/22/2008 AD4155803 P102001108:09 0 207.92 207.92


I've put the data in Word so I could see of there are any spaces or odd
characters.

Because I have both tables as Access tables, I tried changing the data type
to text and that seems to resolve the issue.

Odd thing.
I may have to import the data rather then linking to it, to make this work.

If you have any thoughts I'd be glad to hear them

Kelvin

 

[Jeff Boyce]

Kelvin

You might be able to link to the raw data, then use a query to do the
conversion-to-text. You'd then use that query as a source for your
subsequent queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Lord Kelvan]

if you had to convert it to string there most likly was a problem with
the data types

as i showed the cint() function before there is also cstr() which will
on the fly convert the field to a text format in the query without
changing the actual table

ss then you could do
something like

cstr(AmtSubtotal) <> cstr(AmtDue) as a condition

hope this helps

regards
Kelvan

 

[Kelvin]

thanks for the help!

I'll check these out...