Unique Key and Nulls...

[Jack Doria]

I have an Access 2000 database with a single column primary key
(incrementing number), and a composite unique key. I recently added a field
called InactiveDate. When I added the new field to the unique key
constraint, it now allows duplicate entries. I figure it has to do with the
fact that InactiveDate can contain Null values. I have Ignore Nulls set to
No on the index. If I take the InactiveDate field out of the UK, it
operates as normal for the four other fields in the key.

Does anyone out there know why Access is allowing duplicate entries when one
of the fields has a null value?

Thanks in advance,
Jack Doria

 

[John Vinson]

Does anyone out there know why Access is allowing duplicate entries when one
of the fields has a null value?

Because NULL means "this field has an unknown value, it could be
anything". Since two records with NULL values in a field might have
any value whatsoever in that field (you just don't know what those
values are), it cannot be assumed that they are identical. They might
be, or they might be different.

 

[Jack Doria]

Thanks John. I'll have to come up with another approach.

Jack