Input Masks in Microsoft Access 2013

Govern your data at the user-input level

Businesswoman at desk in office
Tom Merton / Getty Images

It's easier to input clean information into a database the first time than to circle back to fix data-input problems later. Input masks in Microsoft Access 2013 reduce inconsistency in datasets by requiring specific character templates for fields that check the information a user enters during data entry. If the mask's template isn't matched, the database provides a warning message and won't commit the record to the table until the format mismatch is corrected.



For example, an input mask to require users to enter ZIP codes in the format xxxxx-xxxx—where each x is replaced by a numeral—ensures that users supply a full nine-digit ZIP code, including the ZIP+4 extension, and that they do not use alphabetic characters in the field. 

Creating an Input Mask

Build an input mask for a field in an Access 2013  table by using the Microsoft Access Input Mask Wizard:

  1. Open the table containing the field you want to restrict in Design View.
  2. Click the targeted field.
  3. Click the Input Mask box on the General tab of the Field Properties pane at the bottom of the window.
  4. Click the "-" icon to the right of the Input Mask field. This action opens the Input Mask wizard, which walks you through the process.
  5. Select a standard input mask from the first screen of the wizard and click Next to continue.
  6. Review the options on the next screen, which allow you to edit the input mask and choose the placeholder character that Access uses to represent blank spaces that have not yet been filled in by the user. Click Next to continue.
  1. Specify whether Access should display formatting characters in the user input field. For example, this option includes the hyphen between the first five digits and last four digits of a full ZIP code. Similarly, for a telephone number mask, it would include the parentheses, spaces, and hyphen. Click Next to continue.
  1. Click Finish to add the mask. Access displays the template for the requested format in the Field Properties pane for that field.

Editing an Input Mask

The default input masks provided by Microsoft Access 2013 accommodate a wide variety of situations. These default masks include:

  • Telephone numbers
  • Social Security numbers
  • ZIP codes
  • Telephone extensions
  • Passwords
  • Various date and time formats

Use the Input Mask Wizard to edit an input mask to satisfy a need not resolved by one of the default options. Click the Edit List button on the first screen of the Input Mask Wizard to customize a field. Valid characters within an input mask include:

  • 0 - User must enter a numeric digit (0 through 9)
  • 9 - User may enter a numeric digit (0 through 9)
  • # - User may enter a numeric digit, space, +, or -
  • L - User must enter a letter
  • ? - User may enter a letter
  • A - User must enter a letter or numeric digit
  • a - User may enter a letter or numeric digit
  • & - User must enter a character or space
  • C - User may enter characters or spaces

These codes support mandatory and optional characters in the data as indicated by the words "mustand "may." If the input-mask character code represents an optional entry, the user could enter data into the field but also leave it blank.

Periods, commas, hyphens and slashes may be included as placeholders and separators when required.

In addition to these character codes, you can also include special directives in the input masks. These include:

  • ! - Input provided by the user is entered from left to right instead of the normal right-to-left format
  • > - All characters after this symbol are converted to uppercase regardless of how the user inputs them
  • < - All characters after this symbol are converted to lowercase regardless of how the user inputs them
  • \ - The character immediately following the slash will be displayed in the mask as written
  • "- Characters included in quotation marks will be displayed in the mask as written