Lesson 5	Use an input mask to limit data
Objective	Use the Input Mask Wizard to limit data in MS Access 365

Use the Input Mask Wizard to Limit Data Entry in Access 365

In Microsoft Access, an input mask is a field property that guides users to enter data in a required format. It does this by displaying a template (for example, (___) ___-____) and enforcing where digits, letters, and optional characters may appear as the user types.

Input masks are especially useful for values that must follow a consistent pattern—such as phone numbers, employee IDs, postal codes, or other standardized codes. They are primarily a format control, not a full business-rule enforcement system. To truly “limit” data logically (for example, restricting a date to today or earlier), you typically pair an input mask with validation rules, required fields, and appropriate data types.

Input mask vs. validation rule: what each one really does

Access provides two major layers of control that are often confused:

Input Mask: controls the shape of the data (character type and position). Example: forcing exactly 9 digits displayed as ___-__-____.
Validation Rule: controls the meaning of the data (logical constraints). Example: requiring a value between 1 and 100, or preventing future dates.

In real databases, you often use both: the input mask improves user entry consistency, and validation rules enforce business correctness when records are saved.

Using the Input Mask Wizard

The fastest way to apply a standard mask is to use the Input Mask Wizard. The wizard offers common templates and lets you test how the value will look during data entry.

Open the table in Design View.
Input masks are applied at the table-field level, so you must work in Table Design view (not Datasheet view).
Select the field you want to control.
Input masks are most commonly used with Short Text fields (codes, IDs) and can also be used with some Date/Time scenarios depending on your UI requirements.
In the Field Properties pane (bottom area), open the General tab and click the Input Mask property box.
Click the Build button (...) to launch the wizard.
This button appears on the right side of the Input Mask property once the box is active.
Choose a predefined pattern (for example, Social Security Number) and use the Try It box to test entry.
You will see placeholders (often underscores) representing required characters, and any literal formatting characters (such as hyphens) will appear in the template.
Click Next to review or customize the mask settings.
You can typically change the placeholder character and review the mask symbols (for example, 0 meaning “required digit”).
Decide whether Access should store the literal formatting characters (such as hyphens) or store only what the user types.
This choice impacts searching, exporting, and integration. Storing only the digits can be helpful when you want standardized values without punctuation, but storing formatting may be useful for readability in some workflows.
Click Finish to apply the input mask to the field.
Save the table design changes, then switch back to Datasheet View to test the user experience.
You should now see the template guiding entry, and Access will prevent characters that do not fit the mask.

Understanding the three-part input mask format

When the wizard completes, Access writes the mask in a three-part structure separated by semicolons:

mask-pattern ; store-literals ; placeholder

mask-pattern: defines required/optional characters and any literal separators.
store-literals: controls whether extra formatting characters are saved with the value.
placeholder: defines what character appears for “empty” positions (commonly _).

This structure explains why you may see an input mask value that looks “cryptic” at first glance—the wizard is simply writing the mask configuration into a compact format.

Creating an input mask from scratch

If none of the wizard templates match your data, you can create (or fine-tune) a custom input mask. In practice, it is often faster to start with the closest wizard option and then edit it.

When you create a custom mask, focus first on the data pattern (part 1). After that, decide whether formatting characters should be stored (part 2), and finally choose the placeholder character (part 3).

Common input mask characters

The following table summarizes frequently used input mask symbols. These characters define what Access allows at each position in the pattern.

Input mask character	Defines this allowed or required input
0	Required digit (0–9). The user must enter a digit in this position.
9	Optional digit or space. The user may leave this position blank.
#	Optional digit, space, plus (+), or minus (-). Useful for signed numeric input patterns.
L	Required letter (A–Z). The user must enter a letter.
?	Optional letter (A–Z). The user may leave it blank.
A	Required alphanumeric character (letter or digit).
a	Optional alphanumeric character (letter or digit).
C	Optional any character or space (least restrictive).
<	Forces all following characters to lowercase.
>	Forces all following characters to uppercase.
!	Fills the mask from right to left. Useful when leading characters are optional.
\	Treats the next character as a literal (for example, `\S` displays `S`).
. or ,	Displays the decimal placeholder or thousands separator, depending on locale settings.
: ; - /	Displays a literal separator character (commonly used for dates and times).

Modern notes and best practices for Access 365

Prefer the right data type first. For true dates, use Date/Time and validate the value. For codes (postal codes, employee codes), use Short Text with an input mask.
Be cautious with SSN-style masks in real systems. SSNs and other sensitive identifiers should be protected with appropriate security controls, limited access, and (where applicable) organizational compliance policies.
Input masks improve consistency, not correctness. A mask can ensure 9 digits, but it cannot confirm the number is legitimate. Use validation rules or application logic for that.
Date/Time UI behavior can change. In some form controls, applying an input mask to a Date/Time field can interfere with date picker behavior. If you want a calendar picker, test your form after applying the mask and consider relying on Date/Time validation instead.
Test integration/export scenarios. Decide early whether punctuation should be stored. This affects searching and matching values when exporting to Excel, linking to external databases, or integrating with other tools.

Next, you will build on these concepts by selecting fields efficiently in Datasheet and Design views and applying additional field-level controls.

Learn how to select fields in Datasheet and Design views.