Results 1 to 2 of 2
2004-02-10, 02:03 #1
- 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?
2004-02-10, 03:54 #2
- 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.Charlotte