Here's a VERY good question I received from an Access user in South Africa:
Q: Hello. I am fairly new to MS Access. I am using Access 2007. I am setting up a database to keep record of all customers. I've created a table with fields for all their details (Name, Address etc.). One of the field I've created is "Telephone Number" and I've specified an input mask of \(999") "999\-9999 to force the records to conform to a certain format (e.g. (012) 555-3522 - the format for my country). However, there are a few clients outside my country, and so the telephone numbers for them may have extra digits (possibly a dialing code for that country or something like that) which my current input mask of course will not allow. What would your advice be for these few clients where they are the "exception to the rule". Can the input mask be ignored for certain records? Or should i do away with the input mask entirely? Thank you.
A:VERY good question.
You have two options. You can either expand your input mask with a few ### characters which would allow for additional digits in the current field...
OR...
Do what I do in my database which is to have a second phone number field for international customers.
I have an input mask of 000-000-0000 for my US customers because EVERY US (and Canada) phone number HAS to conform to this standard.
I have a second field altogether (Phone2) for international customers. This way all of my data is uniform for 90% of my customers, AND I don't have to worry about special rules for a very small percentage of my data.
No, Access does not really let you use an Input Mask "some" of the time. You either have one or you don't. You could specially validate your customers based on their country, but that would involve a form-level function in an AfterUpdate event - which is probably more than you want to go into right now if you are new to Access.
I cover Input Masks in detail in my Access 102 class