How to Set an Input Mask for Email in Microsoft Access
Microsoft Access 2013 is a relational database integrated with a graphical user interface tool that allows you to manipulate data with more flexibility than a two-dimensional spreadsheet program like Excel. Information in Access is stored in tables, which are often joined and then queried for subsets and combinations of the information they contain.Input Masks and Data Validation are tools that prevent data entry that doesn't follow the rules set for a specific database. Input Masks are used for uniform entry of data like dates, times, and social security and phone numbers. Email addresses vary widely and require a Data Validation rule, which allows data to be entered in a more flexible format than an Input Mask.
Creating a Validation Rule for Email Fields
In any open table, click the "View" button in the Ribbon and navigate to "Design View." All validation rules must be set in the Field Properties of your table, which are not available in the "Datasheet View." Create an Email field, if you haven't already, and ensure the Data Type is "Text," or "Hyperlink" if you need the email address to be actively linked.
Toggle to the Field Properties using "F6" and navigate to "Validation Rule." To ensure all previous changes are saved, click the "Save" button before entering your validation rule.
Copy the following rule into the Validation Rule field:Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*"))This text string sets the requirement that email addresses are formatted with characters separated by an "@" sign.
In the "Validation Text" field, enter the text that displays if someone fails to enter an email address, or enters one in an incorrect format. Sample validation text: "Please submit your email address with an '@' sign and domain name such as 'JennyH@microsoft.com.'"Although the "Validation Text" field is optional, entering your own text prevents a generic or confusing message from displaying when errors occur.
Always save your work when entering or exiting "Design View" unless you made a mistake and wish to exit the view without saving it. Access automatically prompts you to "Save" when changing views.
Tips & Warnings
- Clicking the "F1" function key is particularly helpful in the field properties of Design View. Access Help provides detailed examples and how-to's for everything from Input Masks to Validation Rules.
- Save often when working in Design View, particularly when you're working with fields like Validation Rule where you have to create commands with Boolean Logic. It is also a good idea to back up any database object with a copy and paste before you make changes.