Results 1 to 2 of 2
2008-07-17, 02:34 #1
- Join Date
- Jun 2001
- Crystal Beach, FL, Florida, USA
- Thanked 34 Times in 34 Posts
Query behavior to be aware of (All)
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.
2008-07-17, 05:46 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Query behavior to be aware of (All)
Thanks. Null values often cause unexpected behavior.