Defining Fields


image from book 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 4–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.

image from book
Figure 4–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 4–16.

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

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 4–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. You’ll learn about the Lookup Wizard (and why you shouldn’t use it) in the next chapter.

Table 4–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).

image from book
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 191 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.

image from book

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 Windowsbased 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 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
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 “Setting Control Properties” on page 651 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.[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 “Setting Control Properties” on page 651 or the Access Help topic “Format Property-Number and Currency Types.”

Date/Time[4]

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.[5] 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 “Setting Control Properties” on page 651 or 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 170 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 168 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 ZeroLength Strings,” on page 166 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 188, 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. For an example using a smart tag, see Chapter 12, “Customizing a Form.”

[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] You can also specify a custom format in addition to the built-in ones described here. See Chapter 12 for details.

[5] 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 doublecheck 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.

For details about the properties on the Lookup tab, see “Taking a Look at Lookup Properties,” on page 240.

image from book
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 colorpreference 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 zerolength 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 zerolength 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.

image from book

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 4–3 to design the table shown in Figure 4–17.

Table 4–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

image from book
Figure 4–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. For more information on using validation rules in forms, see Chapter 12.

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 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
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 4-5.

Table 4–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 Q. 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 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
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.

image from book 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 4–18. Note that you can type something in the Try It box below the Input Mask list to try out the mask.

image from book
Figure 4–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 4–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.

image from book
Figure 4–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 4–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 4–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).

image from book
Figure 4–20: You can choose to store formatting characters.

image from book
Figure 4–21: The wizard stores the input mask for PhoneNumber based on the criteria you selected.

Note 

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




Microsoft Office Access 2007 Inside Out
MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)
ISBN: 0735623252
EAN: 2147483647
Year: 2007
Pages: 234

Similar book on Amazon
Access 2007: The Missing Manual
Access 2007: The Missing Manual
Microsoftu00ae Office Access(TM) 2007 Step by Step (Step By Step (Microsoft))
Microsoftu00ae Office Access(TM) 2007 Step by Step (Step By Step (Microsoft))
Microsoft Office Access 2007 Forms, Reports, and Queries
Microsoft Office Access 2007 Forms, Reports, and Queries
Alison Balter's Mastering Microsoft Office Access 2007 Development
Alison Balter's Mastering Microsoft Office Access 2007 Development

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net