Unique fields - not recognising null 1

[TheEnigma]

Hi there,

I want to make a field in a database a unique field, so no one can enter a duplicate value. The unique field will be for an invoice number. In some instances however, when the record is created we do not have the invoice number yet, so we need to come back to that record at a later date when we have received the invoice number.

The only problem is, is if I set the field properties so that no duplicates are aloud, then when nothing is entered in that field it comes back with an error for that record because null value is not allowed. We can't put a dummy code in that field as someone will inevitably duplicate what someone else has put in there.

Is there a way to set up the field to ensure no duplicates are allowed, but to ignore the field if nothing is entered?

 

[RoyVidar]

If it's numeric, I believe setting Required=No and Indexed=No dupes, should work (remember not to have any default value and not put anything in the field).

On text, and if you need to access it programatically, also consider toggling the Allow Zero Lengt property of the field.

Roy-Vidar

 

[TheEnigma]

Hi Roy,

Thanks for that. The only problem I have is that the invoice number is both numeric and alpha numeric.

I am also unsure what you mean in the second paragraph..

 

[RoyVidar]

The field can be text or numeric (but probably not Yes/No, hyperlink, date/time, perhaps currency?, memo, autonumber, OLE object)

If it is text, text fields have an Allow Zero Length property that might be toggled (changed between yes and no). If you need to add Null to a text field programatically, in some versions (2000+?) you'll need to toggle it -> change it, but I don't remember whether it needs to be yes or no.

Roy-Vidar