Force unique, set length field value OR exceptions

[BonnieG]

Is there any way to force a field value to be unique and of a set length, but with exceptions?

Let me explain...

I have a text field in my table called "employee_number" and this value is always one of the following:

• NULL
• an 8-digit number
• the word "External"

What I want to do is to force that field to either be NULL, the word "External", or a unique 8-digit number.

Is this possible? Obviously I can't set the source field in SQL to accept unique values only but I wondered if there was any way around it at form level?

 

[plog]

Seems like you are trying to store 2 distinct items in this column, that's a bad idea. You should make 2 fields for this, one to hold if the employee is external and another to hold their number. The external field would be Yes/No field type and EmployeeID would be an autonumber.

Numbers are still free and, last I checked endless, so give everyone a number. Then when it comes time to display or use this data, you look at the external field--if true return 'External', if false return the employee id formatted as text to 8 characters.

 

[BonnieG]

Unfortunately, an autonumber field wouldn't work in this case. The employee number is imported from our payroll system so not determined by our database. There will be internal staff without a number too.

 

[plog]

Sounds like my plan will work for you then.

 

[BonnieG]

How? I don't understand, sorry.

 

[plog]

Copy my initial post into a text document, and do a search and replace that changes 'autonumber' to 'number'.

The only issue you've found with my method is the datatype of one field. Change that datatype to the one you need.

 

[BonnieG]

Thing is, I can't give everyone a number as they don't have numbers. That's like saying give everyone a fake name. I need to be able to query my employee_number field - if it's got a number in it then that means they're one of our employees and they have an employee number. If I put some random number in there my data will be wrong. Or maybe I'm still not following.

 

[plog]

Maybe I'm not following. Why do you need this to be Null or External? Do they designate different things?

It sounds like whatever the issue is you need to use more than one field to determine this.

 

[gemma-the-husky]

plog has made a sensible suggestion. use a separate (yes/no) field as an "external" indicator. then you can find employees by selecting "external = no".