Specifying the Format of Data in a Field

When you use masks in tables or forms, people entering information can see at a glance the format in which they should make entries and how long they should be. You can use the InputMask property to control how data is entered in text, number, date/time, and currency fields. This property has three sections, separated by semicolons, like the mask for a telephone number, shown here:

!\(000”) “000\-0000;1;#

The first section contains characters that are used as placeholders for the information to be typed, as well as characters such as parentheses and hyphens. Together, all these characters control the appearance of the entry. The following table explains the purpose of the most common input mask characters:

Open table as spreadsheet

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.
“any text”	Access treats the string enclosed in double quotation marks as a literal string.
Password	Creates a password entry box. Any character typed in the box is stored as the character but displayed as an asterisk (*).

Any characters not included in this list are displayed as literal characters. If you want to use one of the special characters in this list as a literal character, precede it with the \ (backslash) character.

The second and third sections of the input mask are optional. Including a 1 in the second section or leaving it blank tells Access to store only the characters entered; including a 0 tells it to store both the characters entered and the mask characters. Entering a character in the third section causes Access to display that character as a placeholder for each of the characters to be typed; leaving it blank displays an underscore as the placeholder.

The input mask !\(000”) “000\-0000;1;# creates this display in a field in either a table or a form:

(###) ###-####

In this example, you are restricting the entry to ten digits-no more and no less. The database user does not enter the parentheses, space, or dash, nor does Access store those characters (although you could display them in your table, form, or report if you set the correct format property). Access stores only the ten digits.

In this exercise, you will use the Input Mask wizard to apply a predefined telephone number input mask to a text field, forcing entered numbers into the (XXX) XXX-XXXX format. You will then create a custom mask to force the first letter entered in another text field to be uppercase (a capital letter).

Use the 03_Accurate database. This practice file is located in the Chapter12 subfolder under SBS_Office2007.

Open the 03_Accurate database. Then display the Field Property Test table in Design view.

1. In the first blank Field Name cell, type fPhone, and leave the data type set to Text.

2. Click the row selector to select the row, and then drag the selected field up to place it just below the ID field.

3. Save the table design, and with fPhone still selected, click Input Mask in the Field Properties area.

4. Click the ellipsis button to the right of the cell to start the Input Mask wizard and display the first page of the wizard. (If you are prompted to install this feature, click Yes.)

5. 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 nines represent optional digits, and the zeros represent required digits. This allows you to enter a telephone number either 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.

6. Change 999 to 000 to require an area code, and then change the placeholder character to #.

7. Click Next.

On the third page of the wizard, you specify whether you want to store the symbols with the data. If you store the symbols, 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.

8. Accept the default selection-to store data without the symbols-by clicking Finish.

Access closes the wizard and displays the edited mask as the Input Mask property.

9. Press 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. Because you told Access to store data without the symbols, nothing is displayed in the second section of the mask. Notice also that Access added double quotation marks to ensure that the closing parenthesis and following space are treated as literals.

Tip 

When you press Enter, a button appears in front of the Input Mask. This is the Property Update Options button , and if you click it, a list of options is displayed. In this case, the only options are to apply the input mask everywhere fPhone is used, and to provide help. This button disappears when you edit any other property or change to a different field.

10. Save your changes, and then switch to Datasheet view.

11. Press the key to move to the new record, then press the key to move to the fPhone field. Type a series of numbers and letters to see how the mask works.

Access formats the first ten numbers you enter as a telephone number, ignoring any letters or additional digits you type. If you type fewer than ten digits and then press Tab or Enter, Access warns that your entry doesn’t match the input mask.

Tip 

An input mask can contain more than only 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 at the beginning of the field, the numbers you type 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.

12. Switch to Design view, and add a new field below fBoolean. Name it LastName. Leave the Data Type setting as the default, Text.

13. Select the new field, click Input Mask, type >L<?????????????????? (18 question marks), and press .

The greater than symbol (>) forces all following text to be uppercase. The L requires a letter. The less than symbol ()< forces all following text to be lowercase. Each question mark 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.

14. Save your changes, return to Datasheet view, type smith in the LastName field of one of the records, and press . Try entering SMITH, and then McDonald.

Regardless of how you type the name, only its the first letter appears in the record capitalized, This type of mask has its limitations, but it can be useful in many situations.

Close the Field Property Test table and the 03_Accurate database.

Tip 

You can create an input mask manually for text, number, date, or currency fields, or you can apply one of several standard masks for text and date fields by using the Input Mask wizard.