Preventing duplicate value data entry

[Afsaneh]

Dear all,
In my table design,Because I need to accept No value , I can not use NO
duplicate index for my field , but I shouldn't let duplicate values in it. to
develop the need I check the value in the data entry form using "Find first"
of the record set. On my own computer it is OK and no duplicate value can be
entered. but users CAN add duplcate value.(mine is office xp but some use
office2003). what should I check? and if there are better solutions please
let me know. tnx in advance.

 

[Allen Browne]

That's not correct: Nulls do not violate a unique index.

In table design, set these properties for your field:
Indexed Yes (No Duplicates)
Required No

You can now have as many nulls as you need, but if there is an entry it
can't be a duplicate of any other existing entry.

 

[Arvin Meyer [MVP]]

Dear all,
In my table design,Because I need to accept No value , I can not use NO
duplicate index for my field , but I shouldn't let duplicate values in it.
to
develop the need I check the value in the data entry form using "Find
first"
of the record set. On my own computer it is OK and no duplicate value can
be
entered. but users CAN add duplcate value.(mine is office xp but some use
office2003). what should I check? and if there are better solutions please
let me know. tnx in advance.

Nulls will only violate a unique index if it is the PrimaryKey, or part of
the PrimaryKey, since PrimaryKeys cannot contain a null.

 

[David W. Fenton]

That's not correct: Nulls do not violate a unique index.

In table design, set these properties for your field:
Indexed Yes (No Duplicates)
Required No

You can now have as many nulls as you need, but if there is an
entry it can't be a duplicate of any other existing entry.

While this is true for a single-field index, a compound index is a
completely different story. In that case, Nulls will mean that your
records aren't unique from a human point of view (though they will
be from the database engine's point of view).

 

[David W. Fenton]

Nulls will only violate a unique index if it is the PrimaryKey, or
part of the PrimaryKey, since PrimaryKeys cannot contain a null.

There's also the case of compound indexes, where Nulls won't
"violate" the index, but may lead to unexpected results.