Part 4 of "Keeping Your Information Accurate in Access 2003 and Access 2002": Using an input mask to restrict data

   Character     Description
   ------------------------------------------------------------------------

   0             Required digit (0 through 9).

   9             Optional digit or space.

   #             Optional digit or space; blank positions are converted 
                 to spaces; plus and minus signs are allowed.

   L             Required letter (A through Z). 

   ?             Optional letter (A through Z). 

   A             Required letter or digit.

   a             Optional letter or digit.

   &             Required character (any kind) or a space. 

   C             Optional character (any kind) or a space. 

   <             All characters that follow are converted to lowercase.

   >             All characters that follow are converted to uppercase.

   !             Characters typed into the mask fill it from left to right.
                 You can include the exclamation point anywhere in the 
                 input mask.

   \             Character that follows is displayed as a literal 
                 character. 

   Password      Creates a password entry box. Any character typed in the 
                 box is stored as the character but is displayed as an 
                 asterisk (*).

1. Open the Field Test database that you created in Part 2 of this series of articles. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
   311168  (http://support.microsoft.com/kb/311168/EN-US/ ) ACC2002: Keeping Your Information Accurate: Part 2: Using the Data Type to Restrict Data
2. Open the Field Property Test table in Design view.
3. Type PhoneField in the first blank Field Name cell, and leave the data type set to Text.
4. Click the row selector to select the row, and then drag the new field to the top of the field list so that it will appear at the left end of the table.
5. Save the table design, and with PhoneField still selected, click Input Mask in the Field Properties section.
6. Click the ... button to the right of the cell to start the Input Mask Wizard and display the first page of the wizard, shown here:
   
   
   Collapse this imageExpand this image

   
   
   
   You can create an input mask by hand for text, number, date, or currency fields, or you can use this wizard to apply one of several standard masks for text and date fields.
7. With Phone Number selected in the Input Mask list, click Next.
   
   The second page of the wizard displays the input mask and gives you the opportunity to change the placeholder character that will indicate what to type.
   
   The exclamation point causes Access to fill the mask from left to right with whatever is typed. The parentheses and hyphen are characters that Access will insert in the specified places. The 9s represent optional digits, and the 0s represent required digits, so you can enter a telephone number with or without an area code.
   
   TIP: Because Access fills the mask from left to right, you would have to press the RIGHT ARROW key to move the insertion point past the first three placeholders to enter a telephone number without an area code.
8. Change 999 to 000 to require an area code, and then change the placeholder character to #.
   
   The dialog box now looks like this:
   
   
   Collapse this imageExpand this image

   
9. Click Next.
   
   On the third page of the wizard, you specify whether you want to store the symbols with the data. If you store them, the data will always be displayed in tables, forms, and reports in this format. However, the symbols take up space, meaning that your database will be larger.
10. Leave the default selection--to store data without the symbols--and then click Finish.
    
    Access closes the wizard and displays the edited mask as the Input Mask property, as shown here:
    
    
    Collapse this imageExpand this image

    
11. Press ENTER to accept the mask.
    
    Access changes the format of the mask to !\(000") "000\- 0000;;#. Notice the two semicolons that separate the mask into its three sections. Since you told Access to store data without the symbols, nothing is displayed in the second section of the mask.
12. Save your changes, and click the View button to return to Datasheet view.
13. Press the DOWN ARROW key to move to the new record, and type a series of at least ten digits and some letters to see how the mask works.
    
    Any letters you type are ignored. The first ten digits are formatted as a telephone number. If you type more than ten digits, they are also ignored. If you type fewer than ten digits and press TAB or ENTER, Access warns you that your entry doesn't match the input mask.
    
    TIP: An input mask can contain more than just the placeholders for the data to be entered. If, for example, you type The number is in front of the telephone number in the Input Mask property, the default entry for the field is The number is (###) #######. Then if you place the insertion point to the left of The and start typing numbers, the numbers replace the # placeholders, not the text. The Field Size setting is not applied to the characters in the mask, so if this setting is 15, the entry is not truncated even though the number of displayed characters (including spaces) is 28.
14. Return to Design view, and add a new field below BooleanField. Name it LastName. Leave the Data Type setting as the default Text.
15. Select the new field, click Input Mask, type >L<?????????????????? (18 question marks), and press ENTER.
    
    The > forces all following text to be uppercase. The L requires a letter. The < forces all following text to be lowercase. Each ? allows any letter or no letter, and there is one fewer question mark than the maximum number of letters you want to allow in the field (19, including the leading capital letter). The Field Size setting must be greater than this maximum.
16. Save your changes, return to Datasheet view, type smith in the LastName field of one of the records, and press TAB. Try entering SMITH, and then McDonald. As you can see, this type of mask has its limitations. But it can be useful in many situations.
17. Save and close the table, and then close the database.

Back to the top

ADDITIONAL RESOURCES

Back to the top