Control data entry formats with input masks

Access for Office 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007

You can assist people typing data correctly into your Access desktop database by providing input masks for fields that include data that is always formatted a certain way. For example, you can use an input mask to make sure that people enter correctly formatted phone numbers into a phone number field.

An input mask only influences whether Access accepts the data – the mask does not change how the data is stored, which is controlled by the field’s data type and other properties. For more information about how data is stored in Access, see the article Introduction to data types and field properties.

Important:  This article only applies to Access desktop databases. Access web apps and web databases do not support input masks.

In this article

About input masks

Characters that define input masks

When to avoid using input masks in Access

Add an input mask to a table field using the Input Mask Wizard

Create custom input masks

Examples of input masks

Using input masks for e-mail addresses

About input masks

An input mask is a string of characters that indicates the format of valid input values. You can employ input masks in table fields, query fields, and controls on forms and reports. The input mask is stored as an object property.

You use an input mask when the formatting of input values is standardised. For example, you might use an input mask with a field that stores phone numbers so that Access requires ten digits of input. If someone enters a phone number without the area code, Access won’t write the data until the area code data is added.

The three parts of an input mask

Input masks are made up one mandatory part and two optional parts, and each part is separated by a semicolon. The purpose of each part is as follows:

  • The first part is mandatory. It includes the mask characters or string (series of characters) along with placeholders and literal data such as, parentheses, periods, and hyphens.
  • The second part is optional and refers to the embedded mask characters and how they are stored within the field. If the second part is set to 0, the characters are stored with the data, and if it is set to 1, the characters are only displayed and not stored. Setting the second part to 1 can save database storage space.
  • The third part of the input mask is also optional and indicates a single character or space that is used as a placeholder. By default, Access uses the underscore (_). If you want to use another character, enter it in the third part of your mask.

For example, this is an input mask for a telephone numbers in the U.S. format: (999) 000-000;0;-:

  • The mask uses two placeholder characters, 9 and 0. The 9 indicates an optional digit (which makes it optional to enter an area code), and each 0 indicates a mandatory digit.

  • The 0 in the second part of the input mask indicates that the mask characters will be stored along with the data.

  • The third part of the input mask specifies that a hyphen () instead of the underscore (_) is to be used as the placeholder character.

Characters that define input masks

The following table lists the placeholder and literal characters for an input mask and explains how it controls data entry:

CharacterExplanation
0User must enter a digit (0 to 9).
9User can enter a digit (0 to 9).
#User can enter a digit, space, plus or minus sign. If skipped, Access enters a blank space.
LUser must enter a letter.
?User can enter a letter.
AUser must enter a letter or a digit.
aUser can enter a letter or a digit.
&User must enter either a character or a space.
CUser can enter characters or spaces.
. , : ; – /Decimal and thousands placeholders, date and time separators. The character you select depends on your Microsoft Windows regional settings.
>Coverts all characters that follow to uppercase.
<Converts all characters that follow to lowercase.
!Causes the input mask to fill from left to right instead of from right to left.
\Characters immediately following will be displayed literally.
“”Characters enclosed in double quotation marks will be displayed literally.

When to avoid using input masks in Access

As useful as they are, input masks are not appropriate in every situation. Don’t use an input mask if following circumstances apply to you:

  • People occasionally need to enter data that doesn’t match the mask. An input mask does not allow exceptions.
  • You plan to use a Date Picker control with a Date/Time field. Input masks are not compatible with the Date Picker control.

Add an input mask to a table field using the Input Mask Wizard

You can use input masks with fields that are set to the Text, Number (except ReplicationID), Currency, and Date/Time data types.

Note: If you use an input mask for a Date/Time field, the Date Picker control becomes unavailable for that field.

  1. In the Navigation Pane, right-click the table and click Design View on the shortcut menu.

  1. Select the field where you want to add the input mask.

  1. Below Field Properties, on the General tab, click the Input Mask property box.

  1. Press the Build button Builder button to start the Input Mask Wizard.

  1. In the Input Mask list, select the type of mask that you want to add.

Input Mask Wizard in Access desktop database

  1. Select Try it and enter data to test how the mask displays.

  1. To keep the input mask without any changes, press Next.

  1. Choose an option for how you want the data to be stored.

  1. Pick Finish and save your changes.

Add an input mask to a query

  1. In the Navigation Pane, right-click the query that you want to change and press Design View on the shortcut menu.

  1. In the query design grid, position the pointer in the column for the field you want to change. You can place the cursor in any row for that field.

  1. Press F4 to launch the property sheet for the field.
  1. Below Field Properties, on the General tab, pick the Input Mask property box.
  1. Press the Build button Builder button to start the Input Mask Wizard, and then follow the instructions in the wizard.

Add an input mask to a form or report control

  1. In the Navigation Pane, right-click your precise form or report that you want to change and select Design View on the shortcut menu.
  1. Right-click the control that you want to change, and then select Properties on the shortcut menu.
  1. On the All tab, pick the Input Mask property box.
  1. Select the Build button Builder button to begin the Input Mask Wizard, and then follow the instructions in the wizard.

Create custom input masks

While the Input Mask Wizard supplies input masks for most common formatting needs, you could often prefer want to customise input masks to better serve your objectives. Input masks can be customised by either modifying the predefined masks from the Input Mask Wizard or by physically editing the Input Mask property for a field where you want the mask applied.

Customse input masks from the Input Mask Wizard

  1. Access the object in Design View, and select the field where you want to add the custom input mask.

  1. Choose the Build Builder button to start the Input Mask Wizard.

  1. Press Edit List. The Customise Input Mask Wizard dialogue box appears.

  1. Move to a new record in the dialogue and enter a new description in the Description text box.

  1. In the Input Mask text box, input characters and placeholders using the allowed characters from the table list.

  1. Pick the Mask Type down arrow and select a suitable mask type.

  1. Select Close. The new input mask displays in the list.

Customise input masks from the field property setting

  1. In the Navigation Pane, right-click the object and select Design View on the shortcut menu.

  1. Pick the field where you want to create the custom input mask.

  1. In the Field Properties area, choose the Input Mask text box, and then enter your custom mask.

  1. Press CTRL+S to acknowledge your changes.

You must manually type the input mask definition for Number and Currency fields.

Examples of input masks

The examples in the following table demonstrate some ways that you can use input masks.

This input maskProvides this type of valueNotes
(000) 000-0000(206) 555-0199In this case, you must enter an area code because that section of the mask (000, enclosed in parentheses) uses the 0 placeholder.
(999) 000-0000!(206) 555-0199
( ) 555-0199
In this case, the area code section uses the 9 placeholder, so area codes are optional. Also, the exclamation point (!) causes the mask to fill in from left to right.
(000) AAA-AAAA(206) 555-TELEAllows you to substitute the last four digits of a U.S. style phone number with letters. Note the use of the 0 placeholder in the area code section, which makes the area code mandatory.
#999-20
2000
Any positive or negative number, no more than four characters, and with no thousands separator or decimal places.
>L????L?000L0GREENGR339M3
MAY R 452B7
A combination of mandatory (L) and optional (?) letters and mandatory numbers (0). The greater-than sign forces users to enter all letters in uppercase. To use an input mask of this type, you must set the data type for the table field to Text or Memo.
00000-999998115-
98115-3007
A mandatory postal code and an optional plus-four section.
>L<??????????????Maria
Pierre
A first or last name with the first letter automatically capitalised.
ISBN 0-&&&&&&&&&-0ISBN 1-55615-507-7A book number with the literal text, mandatory first and last digits, and any combination of letters and characters between those digits.
>LL00000-0000DB51392-0493A combination of mandatory letters and characters, all uppercase. Use this type of input mask, for example, to help users enter part numbers or other forms of inventory correctly.

Using input masks for email addresses

Because email addresses vary widely in the number of characters they contain, input masks are not a good tool for ensuring that email addresses are entered correctly. Instead, we recommend using the Validation Rule and Validation Text properties.

The validation rule displayed in the following table ensures that the email address is entered with one or more characters, then an “@” sign, then one or more characters, then a period, and then one or more characters. For example, tom@example.com would be allowed, but tom@example,com or tom@example would not. If you enter an email address that doesn’t match the validation rule, Access doesn’t accept the input and displays the message in the Validation Text property. If no text is entered in the Validation Text property box, Access displays a generic message.

PropertySetting
Validation RuleIs Null Or ((Like “*?@?*.?*”) And (Not Like “*[ ,;]*”))
Validation Text (optional)Please enter the email address with an ‘@’ sign and the full domain name (for example, ‘frank@contoso.com’).

For more information about using validation rules, as well as step-by-step procedures, see the article Restrict data input by using a validation rule.

Top of Page

Leave a Reply

%d bloggers like this: