Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Thanked 0 Times in 0 Posts

    Duplicates Test Query With Null Field (Access 2000)

    The Duplicate Test Query does not work if one of the fields in the key contains null values. Apparently Null does not equal Null. I tried using the NZ function in a selection query that then became the source of the Duplicate Test query but that does not work either.

    Is there a way to test for duplicates if one of the key fields can (but does not always) contain null values?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Duplicates Test Query With Null Field (Access 2000)

    Nothing equals Null, including Null. <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15> It is a peculiarity of Null values that if you set the field to Required = No and Allow Zero Length = Yes, you can get multiple "unique" records with all other values duplicated but one of the key values Null. For that reason, you need to put tests in the forms you use for data entry to prevent records from being created with nulls in those key fields.

Posting Permissions

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