Acc97 Unique Index problem?

[Steve Marshall]

Hi all,

I have a tiny Access 97 table containing the following fields:

ID Autonumber
Surname Text (50)
Firstname Text(50)

The ID field is the primary index. I also created a *unique* index on the
combination of the Surname and Firstname
fields. The "IgnoreNulls" property on this second index is set to "No".

When entering data in the table, I can enter duplicate values in the Surname
field, if the Firstname field is null!
Surely this should not be possible - the uniqueness should be enforced on
the combination of the two fields, even if one is null.
As soon as I enter something in the Firstname field as well, it will
complain about
duplicate values. Am I missing something really, really obvious here? This
seems like a bug.

Any thoughts appreciated.

 

[TC]

Not to answer your question, but: can you not get two John Smith's?

TC

 

[Lynn Trapp]

When entering data in the table, I can enter duplicate values in the
Surname

field, if the Firstname field is null!
Surely this should not be possible - the uniqueness should be enforced on
the combination of the two fields, even if one is null.

That is exactly what it should do. Null is not equal to Null. Null is
basically an indeterminate value and thus is never equal to itself. To prove
this to yourself run the following SQL statement

Select SomeField
From SomeTable
Where Null = Null;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm