• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint

Defining Fields

Now you’re ready to begin defining the fields for the Companies table as we designed it in Chapter 3. 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 the Tab key once to move to the Data Type column. A button with a down arrow appears on the right side of the Data Type column. Here and elsewhere in Microsoft Access, this type of button signifies the presence of a drop-down list. Click the down arrow or press Alt+Down arrow to open the list of data types, shown in Figure 4-20. In the Data Type column, you can either type a valid value or select from the list of values in the drop-down list. Select AutoNumber as the data type for CompanyID.

Figure 4-20. The drop-down list of data types.

In the Description column for each field, you can enter a descriptive phrase. Access 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.

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 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, since 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 displays some property boxes in the Field Properties area 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 selected; the properties appear with some default values in place, as shown in Figure 4-20.

For details about the values for each property, see “Setting Field Properties,” page 104.

Understanding Field Data Types

Access supports nine 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 tenth 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 (and why you shouldn’t use it) in the next chapter.

Table 4-1. Access Data Types
Data TypeUsageSize
TextAlphanumeric dataUp to 255 characters
MemoAlphanumeric data—sentences and paragraphsUp to about 1 gigabyte (but controls to display a memo are limited to the first 65,535 characters)
NumberNumeric data1, 2, 4, 8, or 16 bytes
Date/TimeDates and times8 bytes
CurrencyMonetary data, stored with 4 decimal places of precision8 bytes
AutoNumberUnique value generated by Access for each new record4 bytes (16 bytes for Replication ID)
Yes/NoBoolean (true/false) data; Access stores the numeric value zero (0) for false, and minus one (–1) for true.1 bit
OLE ObjectPictures, graphs, or other ActiveX objects from another Windows-based applicationUp to about 2 gigabytes
HyperlinkA link “address” to a document or file on the World Wide Web, on an intranet, on a local area network (LAN), or on your local computerUp to about 1 gigabyte

Choosing Field Names

Microsoft Access gives you lots of flexibility when it comes to naming your fields. A field name can be up to 64 characters long, can include any combination of letters, numbers, spaces, and special characters except a period (.), an exclamation point (!), an accent grave (’), and brackets ([ ]); however, the name cannot begin with a space and cannot include control characters (ANSI values 0 through 31). In general, you should give your fields meaningful names and should use the same name throughout for a field that occurs in more than one table. You should avoid using field names that might also match any name internal to Microsoft Access or Visual Basic. For example, all objects have a Name property, so it’s a good idea to qualify a field containing a name by calling it CustomerName or CompanyName. You should also avoid names that are the same as built-in functions, such as Date, Time, Now, or Space. See Microsoft Access Help for a list of all the built-in function names.

Although you can use spaces anywhere within names in Access, you should try to create field names and table names without embedded spaces. Most SQL databases to which Access can attach do not support spaces within names. If you ever want to move your application to a client/server environment and store your data in an SQL database such as Microsoft SQL Server or Oracle, you’ll have to change any names in your database tables that have an embedded space character. As you’ll learn later in this book, table field names propagate into the queries, forms, reports, and data access pages that you design using these tables. So any name you decide to change later in a table must also be changed in all your queries, forms, reports, and data access pages. See “Setting Table Design Options,” page 133, for details about options to automatically propagate changes.

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 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 AM 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 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 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 local area network (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.

Setting Field Properties

You can customize the way Access 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
Data TypeOptions, Description
Field Size Property
TextText can be from 0 through 255 characters long, with a default length of 50 characters.
NumberByte. A single-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 4-byte floating-point number containing values from −3.4 × 1038 through +3.4 × 1038 and up to seven significant digits.
 Double.[*] An 8-byte floating-point number containing values from −1.797 × 10308 through +1.797 × 10308 and up to 15 significant digits.
 Replication ID.[†] A 16-byte globally unique identifier (GUID).
 Decimal. A 12-byte integer with a defined decimal precision that can contain values from −1028 through +1028. The default precision (number of decimal places) is 0 and the default scale is 18.
AutoNumberLong Integer. A 4-byte integer containing values from −2,147,483,648 through +2,147,483,647 when New Values is Random or from 1 to +2,147,483,647 when New Values is Increment.
 Replication ID. A 16-byte globally unique identifier (GUID).
New Values Property
AutoNumber onlyIncrement. 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 Property
Text, MemoYou can specify a custom format that controls how Access displays the data. For details about custom formats, see “Setting Control Properties” on page 451 or the Access Help topic “Format Property—Text and Memo Data Types.”
Number (except Replication ID), Currency, AutoNumberGeneral Number (default). No commas or currency symbols; the number of decimal places shown depends on the precision of the data.
 Currency.[‡] Currency symbol (from the regional settings 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. Percentage—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” on page 451 or the Access Help topic “Format Property—Number and Currency Types.”
Date/Time[**]General Date (default). Combines Short Date and Long Time format (for example, 4/15/2003 5:30:10 PM).
 Long Date. Uses Long Date Style from the regional settings in Windows Control Panel (for example, Tuesday, April 15, 2003).
 Medium Date. 15-Apr-2003.
 Short Date.[††] Uses Short Date Style from the regional settings in Windows Control Panel (for example, 4/15/2003).
 Long Time. Uses Time Style from the regional settings in Windows Control Panel (for example, 5:30:10 PM).
 Medium Time. 5:30 PM.
 Short Time. 17:30.
 You can specify a custom format that controls how Access displays the data. For details about custom formats, see “Setting Control Properties” on page 451 or the Access Help topic “Format Property—Date/Time Data Type.”
Yes/NoYes/No (default).
 You can specify a custom format that controls how Access displays the data. For details about custom formats, see “Setting Control Properties” on page 451 or the Access Help topic “Format Property—Yes/No Data Type.”
Precision Property
Number, DecimalYou 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 Property
Number, DecimalYou can specify the number of decimal digits stored. This value must be less than or equal to the value of the Precision property.
Decimal Places Property
Number (except Replication ID), CurrencyYou 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 Property
Text, Number (except Replication ID), Date/Time, CurrencyYou 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,” page 114, for details.
Caption Property
AllYou 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 Property
Text, Memo, Date/ Time, Hyperlink, Yes/NoYou 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.)
Number, CurrencyAccess sets the property to 0. You can change the setting to a valid numeric value. You can also remove the setting, in which case the field will be Null if the user fails to supply a value. (See also the Required property.)
Validation Rule Property
All (except OLE Object, Replication ID, 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,” page 111, for details.
Validation Text Property
All (except OLE Object, Replication ID, and Auto- Number)You can specify a custom message that Access displays whenever the data entered does not pass your validation rule.
Required Property
All (except AutoNumber)If you don’t want to allow a Null value for the field, set this property to Yes.
Allow Zero Length Property
Text, MemoYou can set the field equal to a zero-length string (“”) if you set this property to Yes. See the sidebar titled “Nulls and Zero-Length Strings” on page 110 for more information.
Indexed Property
All except OLE Object.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,” page 130, for details.
Unicode Compression Property
Text, Memo,As of version 2000, Access stores character fields in an .mdb 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 one byte per character. When you set Unicode Compression to Yes for character fields, Access stores compressible characters in one byte instead of two, 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 two bytes to store all the characters.
IME Mode Property, IME Sentence Mode Property
Text, Memo, HyperlinkOn 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, 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 12, “Customizing a Form.”

[*] Single and Double field sizes use an internal storage format called floating point that can handle very large or very small numbers, but that 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 or Currency data type instead.

[†] In general, the Replication ID field size should be used only in a database that is managed by the Replication Manager.

[‡] 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.

[**] You can also specify a custom format in addition to the built-in ones described here. See Chapter 11 for details.

[††] To help alleviate problems with dates spanning the start of the century, I recommend that you select the Use Four-Digit Year Formatting option in Access. Choose Options from the Tools menu, and then click the General tab to find this option. You should also be sure that your Short Date Style in the regional settings in Windows Control Panel uses a four-digit year. (This is the default in Windows XP.)

Inside Out: Don’t specify a validation rule without validation text

If you specify a validation rule but no validation text, Access 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, I 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,” page 171.

Completing the Fields in the Companies Table

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 LawTrack 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 NameData TypeDescriptionField Size
CompanyIDAutoNumberUnique Company ID 
CompanyNameTextCompany Name50
StateOrProvinceTextState or Province20
PostalCodeTextPostal/Zip Code10
PhoneNumberTextPhone Number15
FaxNumberTextFax Number15
WebSiteHyperlinkWeb site address 
ReferredByNumberContact who referred this companyLong Integer

Figure 4-21. The fields in the Companies table and a validation rule on the CompanyName field.

Nulls and Zero-Length Strings

Relational databases support a special value in fields, called a Null, that indicates an unknown value. In contrast, you can set Text or Memo fields to a zero-length string to indicate that the value of a field is known but the field is empty.

Why is it important to differentiate Nulls (unknown values) from zero-length strings? Here’s an example: Suppose you have a database that stores the results of a survey about automobile preferences. For questionnaires on which there is no response to a color-preference question, it is appropriate to store a Null. You don’t want to match responses based on an unknown response, and you don’t want to include the row in calculating totals or averages. On the other hand, some people might have responded “I don’t care” for a color preference. In this case, you have a known “no preference” answer, and a zero-length string is appropriate. You can match all “I don’t care” responses and include the responses in totals and averages.

Another example might be fax numbers in a customer database. If you store a Null, it means you don’t know whether the customer has a fax number. If you store a zero-length string, you know the customer has no fax number. Access gives you the flexibility to deal with both types of “empty” values.

You can join tables on zero-length strings, and two zero-length strings will compare to be equal. However, for Text, Memo, and Hyperlink fields, you must set the Allow Zero Length property to Yes to allow users to enter zero-length strings. (Yes became the default in Microsoft Access 2002.) Otherwise, Access converts a zero-length or all-blank string to a Null before storing the value. If you also set the Required property of the Text field to Yes, Access stores a zero-length string if the user enters either “” (two double quotes with no space) or blanks in the field.

Nulls have special properties. A Null value cannot be equal to any other value, not even to another Null. This means you cannot join (link) two tables on Null values. Also, the question “Is A equal to B?” when A, B, or both A and B contain a Null, can never be answered “yes.” The answer, literally, is “I don’t know.” Likewise, the answer to the question “Is A not equal to B?” is also “I don’t know.” Finally, Null values do not participate in aggregate calculations involving such functions as Sum or Avg. You can test a value to determine whether it is a Null by comparing it to the special keyword NULL or by using the IsNull built-in function.

Defining Simple Field Validation Rules

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 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 12, “Customizing a Form.”

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 #01/15/2004#.

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 use the special comparison phrase Is Not Null.

Table 4-4. Comparison Symbols Used in Validation Rules
NOTUse 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
INTest for equal to any member in a list; comparison value must be a comma-separated list enclosed in parentheses
BETWEENTest for a range of values; comparison value must be two values (a low and a high value) separated by the AND operator
LIKETest a Text or Memo field to match a pattern string
IS NOT NULLRequires the user to enter a value in the field

Inside Out: A more friendly way to require a field value

When you set the Required property to Yes and the user fails to enter a value, Access displays an unfriendly message:

“The field’<tablename.fieldname>’ cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field.”

I 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 more 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
?Any single character
*Zero or more characters; used 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 RuleTests For
LIKE “#####” orA 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[*]
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

[*] Character string comparisons in Microsoft Access are case-insensitive. So, smith, SMITH, and Smith are all equal.

Defining Input Masks

To assist you in entering formatted data, Access 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 displayed for formatting or stored in the data field.

Table 4-6. Input Mask Definition Characters
Mask CharacterMeaning
0A single digit must be entered in this position.
9A digit or a space can be entered in this position. If the user skips this position by moving the cursor 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 cursor past the position without entering anything, Access stores a space.
LA letter must be entered in this position.
?A letter can be entered in this position. If the user skips this position by moving the cursor past the position without entering anything, Access stores nothing.
AA letter or a digit must be entered in this position.
aA letter or a digit can be entered in this position. If the user skips this position by moving the cursor past the position without entering anything, Access stores nothing.
&A character or a space must be entered in this position.
CAny character or a space can be entered in this position. If the user skips this position by moving the cursor past the position without entering anything, Access stores nothing.
.Decimal placeholder (depends on the setting in the regional settings in Windows Control Panel).
,Thousands separator (depends on the setting in the regional settings in Windows Control Panel).
: ; - /Date and time separators (depends on the settings in the regional settings in Windows 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 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 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. In the first window of the Input Mask Wizard, you have a number of choices for standard input masks that can be generated for you. In this case, click the first one in the list—Phone Number, as shown in Figure 4-22 on the next page. Note that you can type something in the Try It box below the Input Mask selection box to try out the mask.

Figure 4-22. Selecting an input mask in the Input Mask Wizard.

Click the Next button to go to the next window. In this window, shown in Figure 4-23, you can see the mask name, the proposed mask string, a drop-down 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.

Figure 4-23. Selecting the placeholder character in the Input Mask Wizard.

Click Next to go to the next window, 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 window, and then click the Finish button in that window 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).

Figure 4-24. Opting to store formatting characters.

Figure 4-25. The field input mask for PhoneNumber.


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, I 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.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint