Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Working with Field Properties

After you have added fields to your table, you need to customize their properties. Field properties let you control how Access stores data as well as what data the user can enter into the field. The available properties differ depending on which field type you select. You will find the most comprehensive list of properties under the Text field type (see Figure 2.11). The following sections describe each field property.

Field Size: Limiting What's Entered into a Field

The first property is Field Size, available for Text and Number fields only. As mentioned previously, it's best to set the Field Size property to the smallest value possible. For Number fields, a small size means lower storage requirements and faster performance.

Build a table with the following fields and types:

CompanyID :
AutoNumber

CompanyName :
Text

State :
Text

PhoneNumber :
Text

ContactDate :
Date/Time

CreditLimit :
Currency

NOTE

You can find this example, and all others in this chapter, in the CHAP2EX.MDB file included on the book's sample code CD-ROM. Refer to this file if you want to verify that your table structures are correct.

Format: Determining How Data Is Displayed

The second property is Format, available for all but OLE Object fields. It allows you to specify how Access displays your data. Access lets you select from predefined formats or create your own custom formats. The available formats differ, depending on the field's data type. For example, with Access you can select from a variety of Date/Time formats, including Short Date (7/7/03); Long Date (Sunday, July 7, 2003); Short Time (7:17); and Long Time (7:17:11AM). The formats for a Currency field include Currency ($1,767.25); Fixed (1767.25); and Standard (1,767.25).

Set the Format property of the ContactDate field to Medium Date. Switch to Datasheet view and enter some dates in different formats, such as 07/04/03 and July 4, 2003. Notice that, no matter how you enter the dates, as soon as you tab away from the field, they appear in the format dd/mm/yy as 04-Jul-03.

NOTE

The behavior of the Short Date and Long Date formats is dictated by the Regional Options designated in the Control Panel.

TIP

Access 2003 supports Multiple Undo and Multiple Redo actions. You can undo and redo multiple actions in Design view for MDB (Microsoft Database) tables and queries; ADP (Access Data Project) views, stored procedures, and functions; and MDB and ADP forms; reports; data access pages; macros; and modules. This feature allows you to roll forward or roll back your changes in Design view in a similar fashion to working with documents under Microsoft Word or Excel.

TIP

Introduced with Access 2002 are shortcut keys that allow you to easily toggle between the various table views. Ctrl+>, Ctrl+. (period), Ctrl+<, and Ctrl+, (comma). Ctrl+> and Ctrl+. (period) take you to the next view. Ctrl+< and Ctrl+, (comma) take you to the previous view.

Input Mask: Determining What Data Goes into a Field

Another important property is Input Mask, available for Text, Number, Date/Time, and Currency fields. The Format property affects how Access displays data, but the Input Mask property controls what data Access stores in a field. You can use the Input Mask property to control, on a character-by-character basis, what type of character (numeric, alphanumeric, and so on) Access can store, and whether Access requires a particular character. The Input Mask Wizard, shown in Figure 2.12, helps you create commonly used input masks for Text and Date fields only. To access the Input Mask Wizard, click the button to the right of the Input Mask field.

NOTE

The Input Mask Wizard is available only if you selected the Additional Wizards component during setup. If you did not, Access prompts you to install the option on the fly the first time you use it.

For example, the input mask 000-00-0000;;_ (converted to 000\-00\-0000;;_ as soon as you tab away from the property) forces the entry of a valid Social Security number. Everything that precedes the first semicolon designates the actual mask. The zeros force the entry of the digits 0 through 9. The dashes are literals that appear within the control as the user enters data. The character you enter between the first and second semicolon determines whether Access stores the literal characters (the dashes in this case) in the field. If you enter a 0 in this position, Access stores the literal characters in the field; if you enter a 1 or leave this position blank, Access does not store the literal characters. The final position (after the second semicolon) indicates what character Access displays to indicate the space where the user types the next character (in this case, the underscore).

Here's a more detailed example :
In the mask \(999") "000\-0000;;_, the first backslash causes the character that follows it (the open parenthesis) to display as a literal. The three nines allow the user to enter optional numbers or spaces. Access displays the close parenthesis and space within the quotation marks as literals. The first three zeros require values 0 through 9. Access displays the dash that follows the next backslash as a literal. It then requires that the user enter four additional numbers. The two semicolons have nothing between them, so Access does not store the literal characters in the field. The second semicolon is followed by an underscore, so Access displays an underscore to indicate the space where the user types the next character.

Use the Input Mask Wizard to add a mask for the PhoneNumber field, which you should have set up as a Text field.

TIP

When you use an input mask, the user is always in Overtype mode. This behavior is a feature of the product and is not a feature that you can alter.

Caption: A Great Timesaver

The next available property is Caption. The text placed in this property becomes the caption for fields in Datasheet view. Access also uses the contents of the Caption property as the caption for the attached label added to data-bound controls when you add them to forms and reports. The Caption property becomes important whenever you name your fields without spaces. Whatever is in the Caption property overrides the field name for use in Datasheet view, on forms, and on reports.

NOTE

The term

data-bound control refers to a control that is bound to a field in a table or query. The term

attached label refers to the label attached to a data-bound control.

TIP

It's important to set the Caption property for fields

before you build any forms or reports that use them. When you produce a form or report, Access looks at the current caption. If you add or modify the caption at a later time, Access does not modify captions for that field on existing forms and reports.

Default Value: Saving Data-Entry Time

Another important property is the Default Value property, used to specify the default value that Access will place in the field when the user adds new records to the table. Default values, which can be either text or expressions, can save the data-entry person a lot of time. However, Access in no way uses them to validate what the user enters into a field.

TIP

Access automatically carries default values into any queries and forms containing the field. Unlike what happens with the Caption property, this occurs whether you created the default value before or after you created the query or form.

TIP

If you plan to upsize your Access database to a client/server database, you must be aware that default values are not always moved to the server, even if the server supports them. You can set up default values directly on the server, but these values do

not automatically appear when the user adds new records to the table unless the user saves the record without adding data to the field containing the default value. As in auto-numbering, you can implement default values at the form level, with the same drawbacks. If you use the Upsizing Wizard for Access 2002 or 2003 to move the data to Microsoft SQL Server, Access exports default values to your server database.

Enter the following default values for the State, ContactDate, and CreditLimit fields:

State :
CA

ContactDate :
=Date()

CreditLimit :
1000

Switch to Datasheet view and add a new record. Notice that default values appear for the State, ContactDate, and CreditLimit fields. You can override these defaults, if you want.

NOTE

Date() is a built-in Visual Basic for Applications (VBA) function that returns the current date and time. When used as a default value for a field, Access enters the current date into the field when the user adds a new row to the table.

Validation Rule: Controlling What the User Enters in a Field

The Default Value property suggests a value to the user, but the Validation Rule property actually limits what the user can place in the field. The user cannot violate validation rules; the database engine strictly enforces them. As with the Default Value property, this property can contain either text or a valid Access expression, but you cannot include user-defined functions in the Validation Rule property. You also can't include references to forms, queries, or tables in the Validation Rule property.

TIP

If you set the Validation Rule property but not the Validation Text property (covered in the next section), Access automatically displays a standard error message whenever the user violates the validation rule. To display a custom message, you must enter your message text in the Validation Text property.

TIP

If you plan to upsize your Access database to a database server, you should be aware that you cannot always easily export validation rules to the server. You must sometimes re-create them using triggers on the server. No Access-defined error messages are displayed when a server validation rule is violated. Your application should be coded to provide the appropriate error messages. You can also perform validation rules at the form level, but they are not enforced if the data is accessed by other means. If you use the Upsizing Wizard for Access 2003 to move the data to Microsoft SQL Server, the wizard exports the validation rules to the server database.

Add the following validation rules to the fields in your table (Access will place quotes around the state abbreviations as soon as you tab away from the property):

State :
In (CA, AZ, NY, MA, UT)

ContactDate :
<= Date()

CreditLimit :
Between 0 And 5000

TIP

Validation rules entered at a table level are automatically applied to forms and queries built from the table. This occurs whether the rule was entered before or after the query or form was built. If you create a validation rule for a field, Access won't allow Null values to be entered in the field, which means the field can't be left blank. If you want to allow the field to be left Null, you must add the Null to the validation expression:

In (CA, AZ, NY, MA, UT) or Is Null

Validation Text: Providing Error Messages to the User

Use the Validation Text property to specify the error message users see when they violate the validation rule. The Validation Text property must contain text; expressions aren't valid in this property.

Add the following to the Validation Text properties of the State, ContactDate, and CreditLimit fields:

State :
The State Must Be CA, AZ, NY, MA, or UT

ContactDate :
The Contact Date Must Be On or Before Today

CreditLimit :
The Credit Limit Must Be Between 0 and 5000

Try entering invalid values for each of the three fields, and observe the error messages.

Required: Making the User Enter a Value

The Required property is very importantit determines whether you require that a value be entered into a field. This property is useful for foreign key fields, when you want to make sure data is entered into the field. It's also useful for any field containing information that's needed for business reasons (company name, for example).

NOTE

A

foreign key field is a field that is looked up in another table. For example, in the case of a Customers table and an Orders table, both might contain a CustomerID field. In the Customers table, the CustomerID is the primary key field. In the Orders table, the CustomerID is the foreign key field because its value is looked up in the Customers table.

Set the Required property of the CompanyName and PhoneNumber fields to Yes. Switch to Datasheet view and try to add a new record, leaving the CompanyName and PhoneNumber fields blank. Make sure you enter a value for at least one of the other fields in the record. When you try to move off the record, the error message shown in Figure 2.16 appears.

Allow Zero Length: Accommodating Situations with Nonexistent Data

The Allow Zero Length property is similar to the Required property. Use it to determine whether you allow the user to enter a zero-length string ("). A zero-length string isn't the same as a Null (which represents the absence of an entry); a zero-length string indicates that the data doesn't exist for that particular field. For example, a foreign employee might not have a Social Security number. By entering a zero-length string, the data-entry person can indicate that the Social Security number doesn't exist.

Add a new field called ContactName and set its Required property to Yes. Try to add a new record and enter two quotes (") in the ContactName field. You should not get an error message because, in Access 2003, the Allow Zero Length property defaults to Yes. Your zero-length string will appear blank when you move off the field. Return to the Design view of the table. Change the setting for the Allow Zero Length property to No. Go back to Datasheet view and once again enter two quotes in the ContactName field. This time you should not be successful. You should get the error message shown in Figure 2.17.

CAUTION

In previous versions of Access, the default setting for the Allow Zero Length property was No. Under Access 2002 and Access 2003, Microsoft has changed this default setting to Yes. Pay close attention to this new default behavior, especially if you're accustomed to working with prior releases of the product.

TIP

Don't forget that if you want to cancel changes to the current field, press Esc once. To abandon all changes to a record, press Esc twice.

TIP

The Required and Allow Zero Length properties interact with each other. If the Required property is set to Yes and the Allow Zero Length property is set to No, you're being as strict as possible with your users. Not only must they enter a value, but that value can't be a zero-length string.

If the Required property is set to Yes and the Allow Zero Length property is set to Yes, you're requiring users to enter a value, but that value can be a zero-length string. However, if the Required property is set to No and the Allow Zero Length property is set to No, you're allowing users to leave the field Null (blank), but not allowing them to enter a zero-length string.

Finally, if you set the Required property to No and the Allow Zero Length property to Yes, you're being as lenient as possible with your users. In this case, they can leave the field Null or enter a zero-length string.

Indexed: Speeding Up Searches

You use

indexes to improve performance when the user searches a field. Although it's generally best to include too many indexes rather than too few, indexes do have downsides (see the Tip following the Try It example). A general rule is to provide indexes for all fields regularly used in searching and sorting, and as criteria for queries.

Set the Indexed property of the CompanyName, ContactName, and State fields to Yes (Duplicates OK). Click the Indexes button on the toolbar. Your screen should look like Figure 2.18.

To create non-primary-key, multi-field indexes, you must use the Indexes window. You create an index with one name and more than one field. See Figure 2.19, which shows an index called StateByCredit that's based on the combination of the CreditLimit and State fields. Notice that only the first field in the index has an index name. The second field, State, appears on the line below the first field but doesn't have an index name.

Indexes speed up searching, sorting, and grouping data. The downside is that they take up hard disk space and slow down the process of editing, adding, and deleting data. Although the benefits of indexing outweigh the detriments in most cases, you should not index every field in each table. Create indexes only for fields, or combinations of fields, on which the user will search or sort. Do not create indexes for fields that contain highly repetitive data, such as a field that can contain only two different values. Finally, never index Yes/No fields. They are only 1 bit in storage size, and furthermore apply to the previous rule in that they can take on only one of two values. For these reasons, indexes offer no benefits with Yes/No fields.

TIP

Indexes are equally important on a database server. When upsizing an Access database to a non-Microsoft server, no indexes are created. You must re-create all indexes on the back-end database server. If your database server is running Microsoft SQL Server, you can use the Access Upsizing Wizard for Access 2002 or Access 2003 to upsize your Access database. This tool creates indexes for server tables in the place where the indexes exist in your Access tables.

Unicode Compression: Compressing Your Data

Another important property is Unicode Compression. The Unicode Compression property applies to Text and Memo fields only. You use this property to designate whether you want the data in the field to be compressed using Unicode compression. Prior to Access 2000, data was stored in the DBCS (double-byte character set) format, which was designed to store character data for certain languages such as Chinese. With Access 2000, Access 2002, and Access 2003, all character data is stored in the Unicode 2-byte representation format. Although this format requires more space for each character (2 bytes, rather than 1 byte), the Unicode Compression property allows the data to be compressed if possible. If the character set being used allows compression, and the Unicode Compression property is set to Yes, the data in the column is stored in a compressed format.