Results 1 to 2 of 2
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Crystal Beach, FL, Florida, USA
    Thanked 33 Times in 33 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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Query behavior to be aware of (All)

    Thanks. Null values often cause unexpected behavior.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts