No duplicates except for null

[millrat]

Hi,
Is it possible to index a field as no duplicates except for nulls?
eg. I have employee table, each employee has an ID No (unique No). Some employees don't have an Id No (eg contractor). How do I ensure no duplicates for those with an ID No and ignore those who don't have one?
Cheers,
millrat

 

[belovedcej]

you really need to do this in VBA and not the tables. You can't set no duplicates (to my knowledge) unless it also does not allow null values.

Try doing a check on the after update event to see if that ID exists in the table. (will probably need a while loop that terminates on recordset.eof - I'm not good at loops in VBA yet, so you'll want to check the syntax, etc.)

Then an if statement:

If it doesn't, ok, if it does, give a message indicated the ID must be unique.

 

[Golom]

Yes you can

CREATE UNIQUE INDEX myIndex ON myTable (myField)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

You may choose to ignore null values for the index.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[millrat]

Golem,
Good news. But where do I put the code?
millrat