ARCHIVED: In Microsoft Access for Windows, how do I format a cell to automatically put the hyphens in a Social Security number?

Note: At Indiana University, sensitive information (e.g., Social Security numbers, financial data, personnel records) should be collected, manipulated, stored, or shared according to legal and university functional requirements related to the specific use involved, as well as data and security policies of the university. For more information, contact the university Data Steward for the data subject area involved. For a listing of the Committee of Data Stewards, see University Institutional Data Management.

For more information on data security policies at IU, see Data Management Policies and Guidelines. Also, see Best Practices for Securing IT Resources.

In Access, you can use the Input Mask Wizard for many common formatting features, including Social Security number formatting. If the Input Mask Wizard is not installed on your computer, you may receive an error message such as "This feature isn't installed". Follow the appropriate instructions below for installing and using the Input Mask Wizard, or for formatting Social Security numbers without the Wizard:

Access 2010 and 2007

1. From the View menu, select Design View.
2. For the field you wish to auto-format, from the drop-down menu in the "Data Type" column, select Text.
3. On the bottom half of the screen, select the General tab, and then click Input Mask.
4. Click ... to the far right of the Input Mask field.
5. You may be prompted to save the sheet before continuing.
6. Select Social Security Number, and then click Finish.

Access 2002 and 2003

Note: Access 2002 is the version of Access distributed with Office XP.

In Office XP and 2003, use the "Installed on 1st Use" feature to add components. Input Mask, part of the additional wizards in Access, is by default set to install on first use. To install the additional wizards:

1. From the View menu, select Design View.
2. Set the "Data Type" to Text.
3. At the bottom half of the screen, select the General tab, and then click the Input Mask field.
4. To the right of the field, click ... This will automatically inform you if the feature is not installed. If it is not, you'll be prompted to load your Office CD; the additional wizards will then install.
5. To save the table, once the wizard is installed, from the File menu, choose Save.

   Note: The "Field Name:" field will require you to enter some text, and the "Data Type:" field must have Text selected. If the table is not saved properly, then the next step will prompt you to do so. Also, if you do not first fill in the required fields, you will continue to see a "Must save table first" prompt over and over with no indication that the table was not saved properly. To avoid these problems, save the table with the "Field Name:" and "Data Type:" fields filled.

6. Format the cell by clicking ..., then select Social Security number, and then follow the instructions in the wizard.