I ran into a situation in a query that produced results different than what I expected. I understand why now, but I thought I’d post the situation as a heads-up to others.
The basic situation is that I have this standard Contact db, with a master table of Persons and multiple child tables (Phone, email, etc.). Occasionally the same person is enterred twice, so we must merge the 2 Person records (and all the records from child tables). Here was my query for moving the phone records:
UPDATE tblPhone SET tblPhone.PersonID = 9999 WHERE tblPhone.PersonID=1111 AND tblPhone.Phone IS NOT NULL AND tblPhone.Phone Not In (select T.Phone from tblPhone as T Where T.PersonID=9999)
Basically, this just moves all the Phone records of PersonID=1111 to PersonID=9999, provided 2 conditions are met:
#1: The Phone# being moved Is Not Null
#2: Person 9999 doesn’t already have the same Phone#
The query works fines EXCEPT in one instance! This being where Person 9999 had a single phone record already, but the Phone# was NULL! I’m guessing that in this instance, the query evaluated to: … AND tblPhone.Phone Not In (NULL)
So, when Access tried to match tblPHone.Phone to the contents of the NOT IN, the only match was against NULL, which produced a NULL result (and not the True/False I was expecting). As a result, the records didn’t meet the criteria and where not transferred!
I modified my original query adding a test for Null like this:
…tblPhone.Phone Not In (select T.Phone from tblPhone as T Where T.PersonID=9999 AND T.Phone Is Not Null)
After that, all was fine.
I hope this helps someone.
