Unmatched Query is picking up empty fields as not equal (1 Viewer)

Local time
Today, 09:06
Joined
Feb 2, 2020
Messages
54
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.
 

Attachments

  • Test db.accdb
    504 KB · Views: 165

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:06
Joined
Feb 19, 2002
Messages
42,981
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.
 
Last edited:
Local time
Today, 09:06
Joined
Feb 2, 2020
Messages
54
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

You've got your good things, and you've got mine.
Local time
Today, 09:06
Joined
May 21, 2018
Messages
8,463
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.
 

Users who are viewing this thread

Top Bottom