Restricting records based on Nulls in index

[sal]

I have a unique index based on three fields, two of which are required. The third is not always used but when it is, I would like to allow duplication of the first two if the third is unique.

Unfortunately, when it is null, Access does not view the combination of A;B;Null as unique.

The specific example is a unique combination of SurveyID, Species, Stock. Occasionally, two stocks of the same species are enumerated on the same survey.

However, I would like to prevent the same species being entered for the same survey UNLESS the stock is specified and unique.

Is there a way to accomplish this?

 

[Galaxiom]

Enter "" in the Default property of the third field in the table.

This way it stores the ZLS by default instead of a Null and will be accepted as a key component.

Allow Zero Length property must be Yes.