Input Masks in Microsoft Access: Control Data Entry Format

One of the most common data quality problems in Access databases is inconsistent formatting. Phone numbers stored as "(555) 123-4567", "555-123-4567", "5551234567", and "555.123.4567" — all representing the same number, but impossible to sort, search, or display consistently. Input masks solve this by enforcing a specific format as the user types.

What Is an Input Mask?

An input mask is a template that controls what characters a user can type into a field and in what positions. As the user types, Access automatically inserts literal characters (like dashes and parentheses) and validates that each character matches the expected type (digit, letter, etc.).

For example, a phone number input mask (999) 000-0000 automatically inserts the parentheses, space, and dash, and only allows digits in the numbered positions.

The Input Mask Wizard

The easiest way to add an input mask is the wizard:

1. Open the table in Design View (or the form in Design View)
2. Select the field
3. In the Field Properties panel, click the Input Mask property
4. Click the builder button (...) to open the Input Mask Wizard
5. Choose from the pre-built masks (Phone Number, ZIP Code, Social Security Number, etc.)
6. Test the mask by typing in the "Try It" box
7. Choose whether to store the literal characters (like dashes) or just the digits
8. Click Finish

Input Mask Characters

Understanding the mask characters lets you build custom masks:

Character	Meaning
0	Required digit (0-9)
9	Optional digit or space
#	Optional digit, space, plus, or minus
L	Required letter (A-Z, a-z)
?	Optional letter
A	Required letter or digit
a	Optional letter or digit
&	Required any character or space
C	Optional any character or space
<	Convert following characters to lowercase
>	Convert following characters to uppercase
!	Fill from right to left
\	Treat next character as a literal

Common Input Masks

US Phone Number:

(999) 000-0000

US ZIP Code (5 or 9 digit):

00000\-9999

Social Security Number:

000\-00\-0000

Date (MM/DD/YYYY):

00/00/0000

Time (HH:MM AM/PM):

00:00 >LL

Credit Card Number:

0000\ 0000\ 0000\ 0000

License Plate (2 letters + 4 digits):

>LL0000

Product Code (3 uppercase letters + dash + 5 digits):

>LLL\-00000

The Three-Part Input Mask

An input mask can have up to three parts separated by semicolons:

mask;storeLiterals;placeholder

• mask — the mask pattern
• storeLiterals — 0 to store literal characters (dashes, parentheses) in the field; 1 or blank to store only the entered characters
• placeholder — the character shown in empty positions (default is underscore _)

Example: (999) 000-0000;0;_

This stores the parentheses and dash in the field and uses underscore as the placeholder character.

Should you store literals?

• Store literals (0) when you always want the formatted value (e.g., displaying phone numbers)
• Do not store literals (1) when you want to store just the digits and format on display — this keeps the data cleaner and more flexible

Input Masks on Forms vs. Tables

Input masks can be set at the table level (in Table Design View) or at the form level (in Form Design View, on the text box control).

Table-level masks apply everywhere the field is used — forms, datasheets, and anywhere else the field appears.

Form-level masks apply only to that specific form control, overriding the table-level mask.

Best practice: set the mask at the table level for consistency, and override at the form level only when a specific form needs different behavior.

Limitations of Input Masks

Input masks only apply during data entry — they do not validate or reformat data that was imported or entered through other means. Existing data in the field is not affected by adding an input mask.

Input masks conflict with some data types — you cannot use an input mask on a Number field; only Text and Date/Time fields support them.

Users can paste non-conforming data — pasting text that does not match the mask may bypass the mask validation. Add a validation rule as a backup.

International formats — input masks are locale-specific. A US phone mask does not work for international numbers. Consider whether your database needs to support multiple formats.

Combining Input Masks with Validation Rules

For the strongest data quality, combine an input mask with a validation rule:

• Input mask — controls the format during entry
• Validation rule — verifies the stored value meets requirements

For example, a ZIP code field might have:

• Input mask: 00000\-9999
• Validation rule: Len([ZipCode]) = 5 Or Len([ZipCode]) = 10
• Validation text: "Please enter a 5 or 9 digit ZIP code"

Conclusion

Input masks are one of the simplest and most effective data quality tools in Access. A phone number mask alone can eliminate months of inconsistent formatting that would otherwise require a cleanup project. Set them up at the table level for your most important formatted fields, and your data will be cleaner and more consistent from day one.