Unmatched Query is picking up empty fields as not equal

[Michael Barkemeyer]

Hello,
Attached is a db which shows the results of a simple Unmatched Query. I am confused by the results. I would expect the query to return no records at all as the two tables are identical with regards to their record data.
It seems the query is picking up empty or null fields and treating them as if they had different data. How can I change the query def. so that in this case no records are picked up as being different?
I'm sure this will likely have a simple solution, but I can't seem to manage it.
Thank you.

 

[Pat Hartman]

Nulls are neither equal nor not equal. When comparing two fields, one of which is null, the result is null and if you are looking for a match (true) then null is not true.

You need to handle nulls separately. I can't open the db now but you probably need to add criteria to ignore rows with null or specifically select them if that is what you want.

 

[Michael Barkemeyer]

Nulls are neither equal nor not equal. When comparing two fields, one of which is null, the result is null and if you are looking for a match (true) then null is not true.

You need to handle nulls separately. I can't open the db now but you probably need to add criteria to ignore rows with null

Thank you for your reply. The concern I have with ignoring rows with null (not that I would know how to do that), is that other fields for that row may have fields that have changed that I may need to know about.

 

[MajP]

Make qryOne where you do this for each field
sID: [id] & ""

take the original and concatenate a ""

make qryTwo the same way.
Now join 1 and 2. All nulls become empty strings which you can join.