Null or unique value - table data validation rule

[jaworski_m]

Hello,

How to set data validation rule at table level in such a way that only UNIQUE or NULL/EMPTY values can be entered?

If index is set to no duplicates multiple null/empty values cannot be added to a table.



Appreciate any hints.

Marcin

[CJ_London]

you would not use validation rules for this.

In a form you can have some code to ensure a new entry is unique etc, but in a table you would set the field index property to indexed - no duplicates.

for numbers if you want to allow nulls then ensure the default value is left empty
for text change allow zero length property to no

[jaworski_m]

Thank you for reply.

Can you give me a hint how VBA should look like to handle both null and unique values?

Thank you.

[CJ_London]

just rechecked my thinking and you can allow nulls in the index

in table design, click on the indexes option in the ribbon, then select the index required and change ignore nulls to yes (and unique to yes if not already done)