Defining Fields

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 2007, 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 27–16. 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.

Figure 27–16: You can choose the data type of a field from a list of data type options.

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

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

Understanding Field Data Types

Access 2007 supports 10 types of data, each with a specific purpose. You can see the details about each data type in Table 27–1. Access also gives you an eleventh option, Lookup Wizard, to help you define the characteristics of foreign key fields that link to other tables.

Table 27–1: Access Data Types
Open table as spreadsheet

Data Type	Usage	Size
Text	Alphanumeric data	Up to 255 characters
Memo	Alphanumeric data-sentences and paragraphs	Up to about 1 gigabyte (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 minus one (−1) for true	1 bit
OLE Object	Pictures, graphs, or other ActiveX objects from another Windowsbased application	Up to about 2 gigabytes
Hyperlink	A 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 computer	Up to 8,192 characters (each part of a Hyperlink data type can contain up to 2,048 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 gigabytes
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).

Choosing Field Names

Office Access 2007 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 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 Access Help for a list of all the built-in function names.

Although you can use spaces anywhere within names in Access 2007, you should try to create field names and table names without embedded spaces. Many SQL databases to which Access can link (notably Oracle and Ingres) do not support spaces within names. Although SQL Server does allow spaces in names, you must enclose such names in brackets, or use quotes and execute a Set Quoted Identifier On command. 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 most likely 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, and reports 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, and reports. See “Setting Table Design Options” on page 891 for details about options to automatically propagate changes.

If you use reserved words or function names for field names, Access 2007 catches most of these and displays a warning message. This message warns you that the field name you chose, such as Name or Date, is a reserved word and you could encounter errors when referring to that field in other areas of the database application. Access still allows you to use this name if you choose, but take note of the problems it could cause. To avoid potential conflicts, we recommend you avoid using reserved words and built-in functions for field names.

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 2007 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 2007 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 2007 can store and allow you to edit a Microsoft Office Word document, a Microsoft Office Excel spreadsheet, a Microsoft Office 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.

The Attachment data type, newly 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 now 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.

Caution 

You can use the Attachment data type only with databases in the new .accdb file type. If you plan to create a database in the older .mdb format and have users with previous versions of Access use this database, you cannot define any fields as Attachment.

Setting Field Properties

You can customize the way Access 2007 stores and handles each field by setting specific properties. These properties vary according to the data type you choose. Table 27–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 27–2: Field Properties on the General Tab
Open table as spreadsheet

Property	Data Type	Options, Description
Field Size	Text	Text can be from 0 through 255 characters long, with a default length of 50 characters.
	Number	Byte. 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.[1] A 4-byte floating-point number containing values from −3.4×1038 through +3.4×1038 and up to seven significant digits.
		Double.[1] An 8-byte floating-point number containing values from −1.797×10308 through +1.797×10308 and up to 15 significant digits.
		Replication ID.[2] 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 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.[3] 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 the Access Help topic “Format Property-Number and Currency Types.”
	Date/Time	General Date (default). Combines Short Date and Long Time formats (for example, 4/15/2007 5:30:10 PM).
		Long Date. Uses Long Date Style from Regional And Language Options in Control Panel (for example, Sunday, April 15, 2007).
		Medium Date. 15-Apr-2007.
		Short Date.[4] Uses Short Date Style from Regional And Language Options (for example, 4/15/2007).
		Long Time. Uses Time Style from Regional And Language Options (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 the Access Help topic “Format Property-Yes/No Data Type.”
Precision	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	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” on page 870 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, Date/Time, Hyperlink, 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 property, the field will be Null if the user fails to supply a value. (See also the Required property.)
	Number, Currency	Access 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	All (except OLE Object, Replication ID, Attachment, 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” on page 868 for details.
Validation Text	All (except OLE Object, Replication ID, Attachment, and AutoNumber)	You can specify a custom message that Access displays whenever the data entered does not pass your validation rule.
Required	All (except 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,” on page 866 for more information.
Indexed	All except OLE Object 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” on page 888 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 computers 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.
[1] Single and Double field sizes use an internal storage format called floating point that 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 or Currency data type instead. [2] 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. [3] 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. [4] 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. Click the Microsoft Office Button, click Access Options, and then scroll to the General section in the Advanced 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 and Windows Vista; 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 2007 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.

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 2007 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 NULL keyword or by using the IsNull built-in function.

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 Conrad Systems Contacts sample database.) Use the information listed in Table 27–3 to design the table shown in Figure 27–17.

Table 27–3: Field Definitions for the Companies Table
Open table as spreadsheet

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

Figure 27–17: Your fields in the Companies table should look like this. You’ll learn how to define validation rules in the next section.

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 2007 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.

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/2007#.

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 27–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 27–4: Comparison Symbols Used in Validation Rules
Open table as spreadsheet

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 2007 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.”

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 27–5.

Table 27–5: LIKE Wildcard Characters
Open table as spreadsheet

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.

Open table as spreadsheet

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[1]
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 in the first position and ends with exactly four digits
[1] Character string comparisons in Access are case-insensitive. So, smith, SMITH, and Smith are all equal.

Defining Input Masks

To assist you in entering formatted data, Access 2007 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 27–6. You can also embed strings of characters that you want to display for formatting or store in the data field.

Table 27–6: Input Mask Definition Characters
Open table as spreadsheet

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 in Control Panel).
,	Thousands separator (depends on the setting in the Regional And Language Options in Control Panel).
: ; - /	Date and time separators (depend on the settings in the Regional And Language Options 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 2007 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 2007 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 27–18. Note that you can type something in the Try It box below the Input Mask list to try out the mask.

Figure 27–18: You can choose from several built-in input masks in the Input Mask Wizard.

Click the Next button to go to the next page. On this page, shown in Figure 27–19, 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.

Figure 27–19: You can choose the placeholder character in the Input Mask Wizard.

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 27–20, 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 27–21 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 27–20: You can choose to store formatting characters.

Figure 27–21: The wizard stores the input mask for PhoneNumber based on the criteria you selected.

Note 

If you look closely in Figure 27–21, 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.