Create an input mask in Access
Fields like phone numbers, zip codes and credit card numbers often have a standard format. Input masks enable you to enforce the use of such a format whenever such a value is entered into your database. This input mask tutorial shows you how to create an input mask in Access 2010.
Input masks help you gather consistent data
Standardizing the way fields are entered with input masks is a good thing for two reasons. First, they prevent the insertion of incomplete or plain wrong values into your database, like a phone number containing just 4 numbers.
Secondly, when values are entered in a consistent way, it will be easier to sort, group and query on those values later on.
Create an input mask
In this example we will create an input mask for a phone number in international format. The international format for phone numbers is
+<country code>-<area code>-<subscriber code>
- The country code can contain one, two or
three digits and is preceded by a + sign.
- The area code can contain any number of digits, but let's take 6 as a maximum
- The subscriber code can contain any number of digits as well, but lets take 8 as a maximum.
Follow these steps to create the input mask.
- Open your table in design view.
- Select the field you want to create an input mask for.
- Look for the Input Mask field in the field
properties overview at the bottom of the screen.
- In the Input Mask field you can type an input mask directly if
you know how to (and you will after reading the rest of this
tutorial). If you don't yet know how to create an input mask
yourself, click the small button that I marked with the red square.
This will bring up the Input Mask Wizard.
- In the Input Mask Wizard you can choose from a number of standard input masks that Access can create for you. Because I am in the Netherlands, Access is showing me input masks for Dutch zip codes and social security numbers. You may see some other input masks. Choose the Phone Number input mask and click Next. We will modify this input mask to our needs.
- In the next step of the wizard enter +099-0999999-00009999 into
the Input Mask field and in the
Placeholder character dropdown, select the
underscore _ character.
The + sign and the - signs in the Input Mask are literal characters. The Placeholder character indicate where the user can type. The 0 and 0 characters have a special meaning. A 0 indicated a required digit, a 9 character indicates an optional digit. So, in the example, we require
- a country code of at least one digit and max three digits
- an area code of at least one digit and max six digits
- a subscriber code of at least four digits. and max eight digits
- Test the input mask by entering a phone number in the Try It box.
- Click next. In the last window of the Input
Mask Wizard you can tell Access to store the phone numbers with or
without the literal characters defined in the input mask. Choose
whichever is best for your situation.
Input mask special characters
You already saw that the 0 and 9 characters have a special meaning in an input mask. The table below shows all characters that have a special meaning in input masks.
|0||User must enter a digit|
|9||User can enter a digit|
|#||User can enter a digit, space, plus or minus sign. If skipped, Access enters a blank space.|
|L||User must enter a letter|
|?||User can enter a letter|
|A||User must enter a letter or digit|
|a||User can enter a letter or digit|
More special characters can be found here.
When not to use input masks
Input masks are mostly used when you enter "code-like" data that has a standard format and a somewhat standard length into a field.
When you need more advanced control over what data is entered, like validating values that vary in length, validating e-mail addresses or checking if a value doesn't exist in the database already before it is entered you should use validation rules. I will digg into validation rules later in another access tutorial.