Now you’re ready to begin defining the fields for the Companies table that mimics the one you can find in the Conrad Systems Contacts sample database (Contacts.accdb). Be sure the insertion point is in the first row of the Field Name column, and then type the name of the first field, CompanyID. Press Tab once to move to the Data Type column. A button with an arrow appears on the right side of the Data Type column. Here and elsewhere in Access 2010, this type of button signifies the presence of a list. Click the arrow or press Alt+Down Arrow to open the list of data type options, shown in Figure 4-20. In the Data Type column, you can either type a valid value or select from the values in the list. Select AutoNumber as the data type for CompanyID.
In the Description column for each field, you can enter a descriptive phrase. Access 2010 displays this description on the status bar (at the bottom of the Access window) whenever you select this field in a query in Datasheet view or in a form in Form view or Datasheet view. For example, enter Unique Company ID in the Description column for the CompanyID field.
Inside Out: Why Setting the Description Property Is Important
Entering a Description property for every field in your table helps document your application. Because Access 2010 also displays the description on the status bar, paying careful attention to what you type in the Description field can later pay big dividends as a kind of mini-help for the users of your database. Also, because this data propagates automatically, you probably don’t want to type something nonsensical or silly. Typing “I don’t have a clue what this field does” is probably not a good idea—it will show up later on the status bar!
Tab down to the next line, enter CompanyName as a field name, and then choose Text as the data type. After you select a data type, Access 2010 displays some property boxes in the Field Properties section in the lower part of the Table window. These boxes allow you to set properties—settings that determine how Access handles the field—and thereby customize a field. The properties Access displays depend on the data type you select; the properties appear with some default values in place, as shown in Figure 4-20.
Note
For details about the values for each property, see Setting Field Properties.
Access 2010 supports 11 types of data, each with a specific purpose. You can see the details about each data type in Table 4-1. Access also gives you a 12th option, Lookup Wizard, to help you define the characteristics of foreign key fields that link to other tables. You’ll learn about the Lookup Wizard in Chapter 6.
Table 4-1. Access Data Types
Data Type | Usage | Size |
|---|---|---|
Text | Alphanumeric data. | Up to 255 characters. |
Memo | Alphanumeric data—sentences and paragraphs. | Up to about 1 gigabyte (GB), but controls to display a memo are limited to the first 64,000 characters. |
Number | Numeric data. | 1, 2, 4, 8, or 16 bytes. |
Date/Time | Dates and times. | 8 bytes. |
Currency | Monetary data, stored with 4 decimal places of precision. | 8 bytes. |
AutoNumber | Unique value generated by Access for each new record. | 4 bytes (16 bytes for ReplicationID). |
Yes/No | Boolean (true/false) data; Access stores the numeric value zero (0) for false, and –1 for true. | 1 byte. |
OLE Object | Pictures, graphs, or other ActiveX objects from another Windowsbased application. | Up to about 2 GB. |
A link “address” to a document or file on the Internet, on an intranet, on a local area network (LAN), or on your local computer. | Up to 8,192 (each part of a Hyperlink data type can contain up to 2048 characters). | |
Attachment | You can attach files such as pictures, documents, spreadsheets, or charts; each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the size of a database file. | Up to about 2 GB. |
Calculated | You can create an expression that uses data from one or more fields. You can designate different result data types from the expression. | Dependent on the data type of the Result Type property. Text data type result can have up to 243 characters. Memo, Number, Yes/No, and Date/Time should match their respective data types. |
Lookup Wizard | The Lookup Wizard entry in the Data Type column in Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row. | Dependent on the data type of the lookup field. |
For each field in your table, select the data type that is best suited to how you will use that field’s data. For character data, you should normally select the Text data type. You can control the maximum length of a Text field by using a field property, as explained later. Use the Memo data type only for long strings of text that might exceed 255 characters or that might contain formatting characters such as tabs or line endings (carriage returns).
When you select the Number data type, you should think carefully about what you enter as the Field Size property because this property choice will affect precision as well as length. (For example, integer numbers do not have decimals.) The Date/Time data type is useful for calendar or clock data and has the added benefit of allowing calculations in seconds, minutes, hours, days, months, or years. For example, you can find out the difference in days between two Date/Time values.
Inside Out: Understanding What’s Inside the Date/Time Data Type
Use the Date/Time data type to store any date, time, or date and time value. It’s useful to know that Access 2010 stores the date as the integer portion of the Date/Time data type and the time as the fractional portion—the fraction of a day, measured from midnight, that the time represents, accurate to seconds. For example, 6:00:00 A.M. internally is 0.25. The day number is actually the number of days since December 30, 1899 (there will be a test on that later!) and can be a negative number for dates prior to that date. When two Date/Time fields contain only a date, you can subtract one from the other to find out how many days are between the two dates.
You should generally use the Currency data type for storing money values. Currency has the precision of integers, but with exactly four decimal places. When you need to store a precise fractional number that’s not money, use the Number data type and choose Decimal for the Field Size property.
The AutoNumber data type is specifically designed for automatic generation of primary key values. Depending on the settings for the Field Size and New Values properties you choose for an AutoNumber field, you can have Access 2010 create a sequential or random long integer. You can include only one field using the AutoNumber data type in any table. If you define more than one AutoNumber field, Access displays an error message when you try to save the table.
Use the Yes/No data type to hold Boolean (true or false) values. This data type is particularly useful for flagging accounts paid or not paid, or orders filled or not filled.
The OLE Object data type allows you to store complex data, such as pictures, graphs, or sounds, which can be edited or displayed through a dynamic link to another Windows-based application. For example, Access 2010 can store and allow you to edit a Microsoft Word document, a Microsoft Excel spreadsheet, a Microsoft PowerPoint presentation slide, a sound file (.wav), a video file (.avi), or pictures created using the Paint or Draw application.
The Hyperlink data type lets you store a simple or complex “link” to an external file or document. (Internally, Hyperlink is a memo data type with a special flag set to indicate that it is a link.) This link can contain a Uniform Resource Locator (URL) that points to a location on the World Wide Web or on a local intranet. It can also contain the Universal Naming Convention (UNC) name of a file on a server on your LAN or on your local computer drives. The link can point to a file that is in Hypertext Markup Language (HTML) or in a format that is supported by an ActiveX application on your computer.
The Attachment data type, introduced in Access 2007, is very similar to the OLE Object data type in that you can use it to store complex data. However, unlike the OLE Object data type, you can store multiple attachments in a single record. These files are stored in a binary field in a hidden system table. OLE objects usually result in database bloat because the files are not compressed, and Access also stores a bitmap thumbnail of the embedded file that can often be larger than the original file. For the Attachment data type, Access compresses each file, if it isn’t already, and uses the original file rather than a generated thumbnail to minimize the amount of database bloat.
The Calculated data type, newly introduced in Access 2010, allows you to create a calculated result using an expression. The expression can include data from one or more fields. If you have a number field, for example, that holds quantity information for products purchased and a currency field that holds the price of a product, you can create a calculated field that multiplies the quantity and price fields and stores it with a result type of currency. You could also create a calculated field that concatenates first name, middle name, and last name fields and stores it with a result type of text for a field called Full Name. Access recalculates the value of the calculated field any time the dependent fields are changed.
You can customize the way Access 2010 stores and handles each field by setting specific properties. These properties vary according to the data type you choose. Table 4-2 lists all the possible properties that can appear on a field’s General tab in a table’s Design view, and the data types that are associated with each property.
Table 4-2. Field Properties on the General Tab
Property | Data Type | Options, Description |
|---|---|---|
Field Size | Text | Text can be from 0 through 255 characters long, with a default length of 255 characters. |
Number | Byte. A 1-byte integer containing values from 0 through 255. | |
Integer. A 2-byte integer containing values from –32,768 through +32,767. | ||
Long Integer. A 4-byte integer containing values from –2,147,483,648 through +2,147,483,647. | ||
Single.[a] A 4-byte floating-point number containing values from –3.4 × 1038 through +3.4 × 1038 and up to seven significant digits. | ||
Double.[a] An 8-byte floating-point number containing values from –1.797 × 10308 through +1.797 × 10308 and up to 15 significant digits. | ||
Replication ID.[b] A 16-byte globally unique identifier (GUID). | ||
Decimal. A 12-byte integer with a defined decimal precision that can contain values from approximately –7.9228×1028 through +7.9228×1028. The default precision (number of decimal places) is 0 and the default scale is 18. | ||
New Values | AutoNumber only | Increment. Values start at 1 and increment by 1 for each new row. |
Random. Access assigns a random long integer value to each new row. | ||
Format | Text, Memo | You can specify a custom format that controls how Access displays the data. For details about custom formats, see Setting Control Properties for Client Forms, or the Access Help topic “Format Property—Text and Memo Data Types.” |
Number (except Replication ID), Currency, AutoNumber | General Number (default). No commas or currency symbols; the number of decimal places shown depends on the precision of the data. | |
Currency.[c] Currency symbol (from Regional And Language Options in Windows Control Panel) and two decimal places. | ||
Euro. Euro currency symbol (regardless of Control Panel settings) and two decimal places. | ||
Fixed. At least one digit and two decimal places. | ||
Standard. Two decimal places and separator commas. | ||
Percent. Moves displayed decimal point two places to the right and appends a percentage (%) symbol. | ||
Scientific. Scientific notation (for example, 1.05E+06 represents 1.05 × 106). | ||
You can specify a custom format that controls how Access displays the data. For details about custom formats, see Setting Control Properties for Client Forms, or the Access Help topic “Format Property—Number and Currency Types.” | ||
Date/Time[d] | General Date (default). Combines Short Date and Long Time formats (for example, 7/1/2010 5:30:10 PM). | |
Long Date. Uses Long Date Style from the Regional And Language Options item in Control Panel (for example, Thursday, July 1, 2010). | ||
Medium Date. 1-Jul-2010. | ||
Short Date.[e] Uses Short Date Style from the Regional And Language Options item (for example, 7/1/2010). | ||
Long Time. Uses Time Style from the Regional And Language Options item (for example, 5:30:10 PM). | ||
Medium Time. 5:30 PM. | ||
Short Time. 17:30. | ||
Yes/No | Yes/No (default) | |
True/False | ||
On/Off | ||
You can specify a custom format that controls how Access displays the data. For details about custom formats, see Setting Control Properties for Client Forms, or the Access Help topic “Format Property—Yes/No Data Type.” | ||
Calculated | Format options for calculated fields depend on the Result Type. The format options and defaults for the Result Type align with the other data types. | |
Number, Decimal | You can specify the maximum number of digits allowed. The default value is 18, and you can specify an integer value between 1 and 28. | |
Scale | Number, Decimal | You can specify the number of digits stored to the right of the decimal point. This value must be less than or equal to the value of the Precision property. |
Decimal Places | Number (except Replication ID), Currency, Calculated | You can specify the number of decimal places that Access displays. The default specification is Auto, which causes Access to display two decimal places for the Currency, Fixed, Standard, and Percent formats and the number of decimal places necessary to show the current precision of the numeric value for General Number format. You can also request a fixed display of decimal places ranging from 0 through 15. |
Input Mask | Text, Number (except Replication ID), Date/Time, Currency | You can specify an editing mask that the user sees while entering data in the field. For example, you can have Access provide the delimiters in a date field such as __/__/__, or you can have Access format a U.S. phone number as (###) 000-0000. See Defining Input Masks, for details. |
Caption | All | You can enter a more fully descriptive field name that Access displays in form labels and in report headings. (Tip: If you create field names with no embedded spaces, you can use the Caption property to specify a name that includes spaces for Access to use in labels and headers associated with this field in queries, forms, and reports.) |
Default Value | Text, Memo, Number, Date/Time, Currency, Hyperlink, and Yes/No | You can specify a default value for the field that Access automatically uses for a new row if no other value is supplied. If you don’t specify a Default Value, the field will be Null if the user fails to supply a value. (See also the Required property.) |
Validation Rule | All (except OLE Object, Replication ID, Attachment, Calculated, and AutoNumber) | You can supply an expression that must be true whenever you enter or change data in this field. For example, <100 specifies that a number must be less than 100. You can also check for one of a series of values. For example, you can have Access check for a list of valid cities by specifying “Chicago” Or “New York” Or “San Francisco”. In addition, you can specify a complex expression that includes any of the built-in functions in Access. See Defining Simple Field Validation Rules, for details. |
Validation Text | All (except OLE Object, Replication ID, Attachment, Calculated, and AutoNumber) | You can specify a custom message that Access displays whenever the data entered does not pass your validation rule. |
Required | All (except Calculated and AutoNumber) | If you don’t want to allow a Null value in this field, set this property to Yes. |
Allow Zero Length | Text, Memo, Hyperlink | You can set the field equal to a zero-length string (“”) if you set this property to Yes. See the sidebar Nulls and Zero-Length Strings, for more information. |
All except OLE Object, Calculated, and Attachment | You can ask that an index be built to speed access to data values. You can also require that the values in the indexed field always be unique for the entire table. See Adding Indexes, for details. | |
Unicode Compression | Text, Memo, Hyperlink | As of version 2000, Access stores character fields in an .mdb and .accdb file using a double-byte (Unicode) character set to support extended character sets in languages that require them. The Latin character set required by most Western European languages (such as English, Spanish, French, or German) requires only 1 byte per character. When you set Unicode Compression to Yes for character fields, Access stores compressible characters in 1 byte instead of 2, thus saving space in your database file. However, Access will not compress Memo or Hyperlink fields that will not compress to fewer than 4,096 bytes. The default for new tables is Yes in all countries where the standard language character set does not require 2 bytes to store all the characters. |
IME Mode, IME Sentence Mode | Text, Memo, Hyperlink | On machines with an Asian version of Windows and appropriate Input Method Editor (IME) installed, these properties control conversion of characters in kanji, hiragana, katakana, and hangul character sets. |
Smart Tags | All data types except Yes/No, OLE Object, Attachment, and Replication ID | Indicates the registered smart tag name and action that you want associated with this field. When the user views this field in a table datasheet, a query datasheet, or a form, Access displays a smart tag available indicator next to the field. The user can click on the indicator and select the smart tag action to perform. For an example using a smart tag, see Chapter 14. |
Text Align | All data types except Attachment | General (default). Text aligns to the left, but numbers and dates align to the right. |
Left. All data aligns to the left. | ||
Center. All data aligns to the center of the field. | ||
Right. All data aligns to the right. | ||
Distribute. The data is evenly distributed throughout the field. | ||
Text Format | Memo only | Plain Text (default). The text in the Memo field is stored and displayed as plain text. |
Rich Text. You can specify that the data in the Memo field can be formatted as rich text. Access applies HTML formatting tags to your data. | ||
Append Only | Hyperlink and Memo | You can specify to see column history for this field. When you change the field’s data, the data change and time stamp are recorded and appended to the version history of the field. |
Show Date Picker | Date/Time only | For Dates (default). Displays the built-in date picker control to select a date when the field receives focus in a table datasheet or query. |
Never. The built-in date picker control is not shown when the field receives focus in a table datasheet or query. | ||
Expression | Calculated | The expression used to calculate the value for this column. The expression can use the value of one or more fields in the same table and can be up to 65,000 characters in length. |
Result Type | Calculated | For calculated fields, you need to provide the data type that results from the expression you use for the field. The result type can be Double, Integer, Long Integer, Single, Replication ID, Decimal, Text, Date/Time, Memo, Currency, or Yes/No. |
[a] Single and Double field sizes use an internal storage format called floating point, which can handle very large or very small numbers, but which is somewhat imprecise. If the number you need to store contains more than 7 significant digits for a Single or more than 15 significant digits for a Double, the number will be rounded. For example, if you try to save 10,234,567 in a Single, the actual value stored will be 10,234,570. Likewise, Access stores 10.234567 as 10.23457 in a Single. If you want absolute fractional precision, use Decimal field size instead. [b] In general, you should use the Replication ID field size only in an Access 2003 format and earlier database that is managed by the Replication Manager. [c] Note that Currency, Euro, Fixed, and Standard formats always display two decimal places regardless of the number of actual decimal places in the underlying data. Access rounds any number to two decimal places for display if the number contains more than two decimal places. [d] You can also specify a custom format in addition to the built-in ones described here. See Chapter 14 for details. [e] To help alleviate problems with dates spanning the start of the century, we recommend that you select the Use Four-Digit Year Formatting check box in Access. To do this, click the File tab on the Backstage view, click Options, and then scroll to the General section in the Client Settings category to find this option. You should also be sure that your Short Date Style in the Regional And Language Options dialog box uses a four-digit year. (This is the default in Windows XP, Windows Vista, and Windows 7; you can double-check your settings by accessing Regional And Language Options within Control Panel.) | ||
Inside Out: Don’t Specify a Validation Rule Without Validation Text
If you specify a validation rule but no validation text, Access 2010 generates an ugly and cryptic message that your users might not understand:
“One or more values are prohibited by the validation rule ‘<your expression here>’ set for ‘<table name.field name>’. Enter a value that the expression for this field can accept.”
Unless you like getting lots of support calls, we recommend that you always enter a custom validation text message whenever you specify a validation rule.
For details about the properties on the Lookup tab, see Taking a Look at Lookup Properties.
You now know enough about field data types and properties to finish designing the Companies table in this example. (You can also follow this example using the tblCompanies table from the Conrad Systems Contacts sample database.) Use the information listed in Table 4-3 to design the table shown in Figure 4-21.
Table 4-3. Field Definitions for the Companies Table
Field Name | Data Type | Description | Field Size |
|---|---|---|---|
CompanyID | AutoNumber | Unique Company ID | |
CompanyName | Text | Company Name | 50 |
Department | Text | Department | 50 |
Address | Text | Address | 255 |
City | Text | City | 50 |
County | Text | County | 50 |
StateOrProvince | Text | State or Province | 20 |
PostalCode | Text | Postal/Zip Code | 10 |
PhoneNumber | Text | Phone Number | 15 |
FaxNumber | Text | Fax Number | 15 |
Website | Hyperlink | Website address | |
ReferredBy | Number | Contact who referred this company | Long Integer |
To define a simple check on the values that you allow in a field, enter an expression in the Validation Rule property box for the field. Access 2010 won’t allow you to enter a field value that violates this rule. Access performs this validation for data entered in a Table window in Datasheet view, in an updateable query, or in a form. You can specify a more restrictive validation rule in a form, but you cannot override the rule defined for the field in the table by specifying a completely different rule in the form. For more information on using validation rules in forms, see Chapter 14.
In general, a field validation expression consists of an operator and a comparison value. If you do not include an operator, Access assumes you want an “equals” (=) comparison. You can specify multiple comparisons separated by the Boolean operators OR and AND.
It is good practice to always enclose text string values in quotation marks. If one of your values is a text string containing blanks or special characters, you must enclose the entire string in quotation marks. For example, to limit the valid entries for a City field to the two largest cities in the state of California, enter “Los Angeles” Or “San Diego”. If you are comparing date values, you must enclose the date constants in pound sign (#) characters, as in #07/1/2010#.
You can use the comparison symbols to compare the value in the field to a value or values in your validation rule. Comparison symbols are summarized in Table 4-4. For example, you might want to ensure that a numeric value is always less than 1000. To do this, enter <1000. You can use one or more pairs of comparisons to ask Access to check that the value falls within certain ranges. For example, if you want to verify that a number is in the range of 50 through 100, enter either > = 50 And < = 100 or Between 50 And 100. Another way to test for a match in a list of values is to use the IN comparison operator. For example, to test for states surrounding the U.S. capital, enter In (“Virginia”, “Maryland”). If all you need to do is ensure that the user enters a value, you can enter the special comparison phrase Is Not Null.
Table 4-4. Comparison Symbols Used in Validation Rules
Operator | Meaning |
|---|---|
NOT | Use before any comparison operator except IS NOT NULL to perform the converse test. For example, NOT > 5 is equivalent to <=5. |
< | Less than |
<= | Less than or equal to |
> | Greater than |
>= | Greater than or equal to |
= | Equal to |
<> | Not equal to |
IN | Test for equal to any member in a list; comparison value must be a comma-separated list enclosed in parentheses |
BETWEEN | Test for a range of values; comparison value must be two values (a low and a high value) separated by the AND operator |
LIKE | Test a Text or Memo field to match a pattern string |
IS NOT NULL | Requires the user to enter a value in the field |
Inside Out: A Friendlier Way to Require a Field Value
When you set the Required property to Yes and the user fails to enter a value, Access 2010 displays an unfriendly message:
“You must enter a value in the ‘<tablename.fieldname>’ field.”
We recommend that you use the Validation Rule property to require a value in the field and then use the Validation Text property to generate your own specific message.
If you need to validate a Text, Memo, or Hyperlink field against a matching pattern (for example, a postal code or a phone number), you can use the LIKE comparison operator. You provide a text string as a comparison value that defines which characters are valid in which positions. Access understands a number of wildcard characters, which you can use to define positions that can contain any single character, zero or more characters, or any single number. These characters are shown in Table 4-5.
Table 4-5. LIKE Wildcard Characters
Character | Meaning |
|---|---|
? | Any single character |
* | Zero or more characters; use to define leading, trailing, or embedded strings that don’t have to match any specific pattern characters |
# | Any single digit |
You can also specify that any particular position in the Text or Memo field can contain only characters from a list that you provide. You can specify a range of characters within a list by entering the low value character, a hyphen, and the high value character, as in [A-Z] or [3-7]. If you want to test a position for any characters except those in a list, start the list with an exclamation point (!). You must enclose all lists in brackets ([ ]). You can see examples of validation rules using LIKE here.
Validation Rule | Tests For |
|---|---|
LIKE “#####” or | A U.S. 5-digit ZIP Code |
LIKE “#####-####” | A U.S. 9-digit ZIP+ Code |
LIKE “[A-Z]#[A-Z]#[A-Z]#” | A Canadian postal code |
LIKE “###-##-####” | A U.S. Social Security Number |
LIKE “Smith*” | A string that begins with Smith[a] |
LIKE “*smith##*” | A string that contains smith followed by two numbers, anywhere in the string |
LIKE “??00####” | An eight-character string that contains any first two characters followed by exactly two zeros and then any four digits |
LIKE “[!0-9BMQ]*####” | A string that contains any character other than a number or the letter B, M, or Q in the first position and ends with exactly four digits |
[a] Character string comparisons in Access are case-insensitive. So, smith, SMITH, and Smith are all equal. | |
To assist you in entering formatted data, Access 2010 allows you to define an input mask for Text, Number (except Replication ID), Date/Time, and Currency data types. You can use an input mask to do something as simple as forcing all letters entered to be uppercase or as complex as adding parentheses and hyphens to phone numbers. You create an input mask by using the special mask definition characters shown in Table 4-6. You can also embed strings of characters that you want to display for formatting or store in the data field.
Table 4-6. Input Mask Definition Characters
Mask Character | Meaning |
|---|---|
0 | A single digit must be entered in this position. |
9 | A digit or a space can be entered in this position. If the user skips this position by moving the insertion point past the position without entering anything, Access stores nothing in this position. |
# | A digit, a space, or a plus or minus sign can be entered in this position. If the user skips this position by moving the insertion point past the position without entering anything, Access stores a space. |
L | A letter must be entered in this position. |
? | A letter can be entered in this position. If the user skips this position by moving the insertion point past the position without entering anything, Access stores nothing. |
A | A letter or a digit must be entered in this position. |
a | A letter or a digit can be entered in this position. If the user skips this position by moving the insertion point past the position without entering anything, Access stores nothing. |
& | A character or a space must be entered in this position. |
C | Any character or a space can be entered in this position. If the user skips this position by moving the insertion point past the position without entering anything, Access stores nothing. |
. | Decimal placeholder (depends on the setting in the Regional And Language Options item in Control Panel). |
, | Thousands separator (depends on the setting in the Regional And Language Options item in Control Panel). |
: ;-/ | Date and time separators (depends on the settings in the Regional And Language Options item in Control Panel). |
< | Converts to lowercase all characters that follow. |
> | Converts to uppercase all characters that follow. |
! | Causes the mask to fill from right to left when you define optional characters on the left end of the mask. You can place this character anywhere in the mask. |
Causes the character immediately following to be displayed as a literal character rather than as a mask character. | |
“literal” | You can also enclose any literal string in double quotation marks rather than use the \ character repeatedly. |
An input mask consists of three parts, separated by semicolons. The first part defines the mask string using mask definition characters and embedded literal data. The optional second part indicates whether you want the embedded literal characters stored in the field in the database. Set this second part to 0 to store the characters or to 1 to store only the data entered. The optional third part defines a single character that Access 2010 uses as a placeholder to indicate positions where data can be entered. The default placeholder character is an underscore (_).
Perhaps the best way to learn to use input masks is to take advantage of the Input Mask Wizard. In the Companies table of the Contact Tracking database, the PhoneNumber field could benefit from the use of an input mask. Click the PhoneNumber field in the upper part of the Table window in Design view, and then click in the Input Mask property box in the lower part of the window. You should see a small button with three dots on it (called the Build button) to the right of the property box.
Click the Build button to start the Input Mask Wizard. If you haven’t already saved the table, the wizard will insist that you do so. Save the table and name it Companies. When Access 2010 warns you that you have not defined a primary key and asks if you want to create a primary key now, click No. We’ll define a primary key in the next section. On the first page, the wizard gives you a number of choices for standard input masks that it can generate for you. In this case, click the first one in the list—Phone Number, as shown in Figure 4-22. Note that you can type something in the Try It box below the Input Mask list to test the mask.
Click Next to go to the next page. On this page, shown in Figure 4-23, you can see the mask name, the proposed mask string, a list from which you select the placeholder character, and another Try It box. The default underscore character (_) works well as a placeholder character for phone numbers.
Click Next to go to the next page, where you can choose whether you want the data stored without the formatting characters (the default) or stored with the parentheses, spaces, and hyphen separator. In Figure 4-24, we’re indicating that we want the data stored with the formatting characters. Click Next to go to the final page, and then click the Finish button on that page to store the mask in the property setting. Figure 4-25 shows the resulting mask in the PhoneNumber field. You’ll find this same mask handy for any text field that is meant to contain a U.S. phone number (such as the phone number fields in the Contacts table).
Note
If you look closely at Figure 4-25, you can see a backslash before the area code and quotation marks around the second parenthesis. When you complete the Input Mask Wizard, Access initially does not display these extra characters. After you click off that field or save the table, Access adds the missing characters. The mask generated by the wizard is incorrect, but the table editor fixes it before saving.
Caution
Although an input mask can be very useful to help guide the user to enter valid data, if you define an input mask incorrectly or do not consider all possible valid values, you can prevent the user from entering necessary data. For example, we just showed you how to build an input mask for a U.S. telephone number, but that mask would prevent someone from entering a European phone number correctly.