Allow null in unique index (A2003)

Topic

New Reply

I have created a compound index on four fields in a table, and added the Unique property to prevent duplicate values from appearing in the table. Is there a way to modify the index to allow me to omit one or more of the values (ie, the field is not significant for some combinations of the other three fields) and still have uniqueness enforced for the remaining three fields? When I try entering duplicated values of the three fields, Access allows them to be entered. Tried making another index on the three remaining fields, but uniqueness is violated by the records for which the fourth field is significant.

Thanks

Reply | Quote

Replies

Null is not equal to null. Null is a void, a blank, an absence, so it is not equal to anything, not even to another null. So Access does not consider the following records to be equal:

“John”, 37, 12, (null)
“John”, 37, 12, (null)

One way to get around this is to define a default value for each field, and to treat this default value as ‘missing’.
If you don’t like that, you’ll have to check your definition of uniqueness in the Before Update event of the form used to enter/edit records; this could become quite complicated.

Reply | Quote

OK – I get it now. Thanks again!
Looks like I need to define default values in the four lookup tables.

Reply | Quote