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 the University Information Policy Office (UIPO) Institutional Data Management web site.

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 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 in the box beside Input Mask.
   
   
4. To the right of the field, click  ...  (the ellipsis icon that appears). 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. Also, the "Data Type:" field must have Text selected. (This is a change from previous versions of Access where Number was the proper selection.) 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  ...  (the ellipsis), then select Social Security number, and then follow the instructions in the Wizard.

Access 2000

Office 2000's "Installed on 1st Use" feature makes it easy to add components when needed. Input Mask, part of the Additional Wizards in Access 2000, is by default set to install on first use. To install the Additional Wizards:

1. From the View menu, select Design View.
   
   
2. In the "Data Type" field, select number.
   
   
3. At the bottom half of the screen, select the General tab, and then click Input Mask or the box beside Input Mask.
   
   
4. To the right of the field, click  ...  (the ellipsis). This will automatically inform you if the feature is not installed. If it is not, it will ask you to load your Office 2000 CD and will then install the Additional Wizards.
   
   
5. Once the Wizard is installed, if necessary, format the cell by clicking  ...  (the ellipsis), selecting Social Security number, and following the instructions in the Wizard.

Access 97

1. Close any Microsoft applications.
   
   
2. Insert your Microsoft Access or Microsoft Office Setup CD into the appropriate drive.
   
   
3. On the Installation screen, choose Custom.
   
   
4. Highlight MS Access. Click the Change Option button.
   
   
5. Check Advanced Wizards, and then click OK.
   
   
6. When the Setup screen reappears, click Continue.

To format the cell without the Input Mask Wizard

1. In Access, from the View menu, select Design View. In the "Data Type" field, select number.
   
   
2. In the "Input Mask" field, enter the following text for Access 2000: 000-00-000;;_ For Access 97 type: 000\-00\-0000;;_

Now you can enter Social Security numbers in a cell, and Access will add the hyphens automatically.

Comments/Questions/Corrections