Preventing duplicate records involving null values

[CarlyS]

I have a table with a multi-field unique index:
PatientID
TestId
TestDate
Examiner

The problem is, date and/or examiner can be blank until that information is updated. I want those null values to be understood as actual values so that multiple instances of that "waiting to be updated" record do not occur. Is it possible for Access to understand my indexes in the following way?

PatientID----TestID----TestDate----Examiner----(Index)
1------------1---------07/18/05----AD----------1107/18/05AD
1------------1---------[blank]------[blank]------11

and therefore not allow another record like the second one to be added? The ignore nulls property of my index doesn't seem to affect this issue?

Thanks!
Carly

 

[Pat Hartman]

You shouldn't be storing records until you have information to put in them, especially key information.

You cannot add rows with null values in any of the required fields and this is especially a problem when you need the fields to make a key. I suggest rethinking the design since the only alternative is storing "dummy" values in the unknown fields and that is a really poor solution.