Contents

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.

  1. Open your table in design view.
  2. Select the field you want to create an input mask for.
  3. Look for the Input Mask field in the field properties overview at the bottom of the screen.

    Field properties input mask
  4. 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.

    Input Mask Wizard Access 2010
  5. 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.
  6. 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.

    phone number input mask

    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
    1. a country code of at least one digit and max three digits
    2. an area code of at least one digit and max six digits
    3. a subscriber code of at least four digits. and max eight digits
  7. Test the input mask by entering a phone number in the Try It box.
  8. 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.

    Choose input mask storage

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.