Assigning Field Properties

Field properties are crucial to maintaining and enhancing data integrity and presentation. The properties you set in the lower pane of the Design window dramatically affect the way data is used and displayed in forms, queries, and reports. These objects inherit the field properties from the underlying table.

Some field properties can be set within these other objects, and they may (or may not ) override the settings in the table (I'll leave most of that discussion for Chapter 6, "Entering, Editing, and Displaying Data"). Nevertheless, it will simplify your life greatly if, for the most part, you set field properties just once in tables and let them be.

Instead of wasting a lot of paper giving you all the specifications for field propertiesparticularly Field Size and Format, which have many detailsI've tried to summarize important points and offer observations that might be useful. You can easily access the detailed specs on any field property in Access Help: In the lower pane of Design view, click inside the field property and press F1.

Field Size

You might initially be confused by the Field Size property because its settings change dramatically depending on the data type (see Figure 5.10). The property is significant for Text and, especially, Number data types.

Figure 5.10. The Field Size property depends on the data type. The current field is Units In Stock, which has the Number data type. The selections in the Field Size property are for Number fields.

Text

For the Text data type, the Field Size determines the number of alphanumeric characters that can be entered, up to a maximum of 255. The default setting of 50 can be adjusted in the Default Field Sizes section of the Tables/Queries tab of the Options dialog box, available from the Tools menu.

Field Size can be extremely useful for maintaining data integrity. In a field reserved for U.S. states, for example, you can make sure that only two-letter abbreviations are entered by setting the field size to 2. That still won't guarantee that ME and not MA is entered for Maine; but it will make sure you don't enter Mer, Main, or Maine for Maine.

Numbers

The Field Size for numbers determines the number of bytes that can be stored. But I think it's more useful to think of the property as controlling the size of the number and whether fractions can be included. Byte, Integer, and Long Integer, in that order, can store increasingly larger whole numbers (no fractions). Decimal, Single, and Double, in that order, can store increasingly larger numbers with their fractions (Decimal can store yet more decimal places). The AutoNumber data type is always a Long Integer (excluding Replication ID, an advanced topic I ignore here).

Access recommends that you use the smallest possible field size because your data will be processed faster. But with a relatively small database, on today's powerful machines, the time saved might not be great.

NOTE

There is no Field Size property for Date/Time, Currency, Yes/No, OLE Object, Memo, and Hyperlink data types.

Format

The Format property determines how your data is displayed, versus how it is stored. Obviously, however, you can't format what isn't stored in the first place, so this property is dramatically affected by what values are actually in your database. For example, suppose you set the Field Size for the Number data type as Long Integer, which allows no fractions. Access still lets you choose a Fixed or Standard format for the field, both of which show decimal places. But you're going to see just zeroes in those decimal places because there aren't any meaningful places to show.

Your choices in the Format property naturally depend greatly on the data type. The selections in the Format property of a Date/Time field allow you to display a date variously as 9/17/2004, 17-Sep-2004, or September 17, 2004. These formats are obviously peculiar to this data type. In a Number field, your options are much different.

NOTE

The Format propertyparticularly for Date/Time and Number data typesis also substantially affected by Regional Settings in the Windows Control Panel.

You can select predetermined formats by opening the drop-down menu and choosing a format that fits your preferences. If the defined format selections are not to your liking, you can create customized formats. Customized Date/Time formats are fairly easy to create; Text and Number formats of some sophistication are more difficult.

Formats coexist somewhat uneasily with input masks, a property that provides a template for entering data. In Chapter 6, I discuss input masks and their relationship to formats at some length, so I postpone further comment on the Format property until then.

Caption

The Caption property shouldn't give you any problems. Access field names are often difficult to decipher, at least at first glance. Entering a caption enables you to name the field so that it obeys Access rules and conventions and, at the same time, displays an everyday English label for the column in Datasheet view. Just type in the text string in the Caption property that you would like displayed.

NOTE

A caption is not an alias, an alternative field name. With a caption, there is still only one field name; you're just using a label to make it more understandable.

Again, the Caption property is inherited by objects built on the underlying table. So even if you don't have any trouble distinguishing between LastName and Last Name, you might want to enter the latter as a caption if you're planning to create any reports for external use.

NOTE

You can double-click in the Column Selector of a datasheet and change the column name. I wish this weren't possible. When you edit the name of a column, you're not only editing its caption, but you're editing the actual field name, something you only rarely need or want to do. If you didn't know about this bit of functionality, forget I told you about it. If you did, don't use it.

Decimal Places

Oddly, the harmless-sounding Decimal Places property causes much confusion. I've taken a stab at simplification, but I know I haven't covered all possible outcomes. If decimals are crucial to your work, be sure to verify your settings with test data.

The first thing to remember about the Decimal Places property is that it has no effect on how many decimals are stored in a Number fieldonly the places displayed. The Field Size property determines what's stored. If it's set to Byte, Integer, or Long Integer, Access won't accept or store decimals. The Single setting stores up to 7 places, and the Double setting stores up to 15.

If you set the Decimal Places property to Auto, the number of places is determined by the Format property. If Format is set to Fixed or Standard, the number of decimal places displayed is two (assuming English United States is your Windows Regional Settings). Thus, a value of 6.7789 entered and stored in a Number field with a Field Size of Single, a Format of Fixed, and Decimal Places of Auto is displayed as 6.78. Access rounds up decimals of 5 and above, and rounds down decimals of 4 and below.

If the Decimal Places property is set to a specific number, that number of places is displayed, regardless of the format. In the previous example, if Decimal Places was 3, the number displayed would be 6.779.

Setting the Decimal Places property has no effect if the Format is General Number. In this case, if you enter 6.7789 and Decimal Places is 1, the display is still 6.7789.

NOTE

For even greater precision in the storage of decimal places, a Field Size property called Decimal can store up to 28 places.

Validation Rule and Validation Text

Among the most effective devices for maintaining data security is the Validation Rule property. You enter an expression in the Validation Rule property that restricts the data that can be entered. Validation rules are often used as a means of implementing business rules.

For example, suppose that in a Merchandise table, you have a PurchasePrice field for the unit cost of the item. Let's say you don't want any items in your inventory that cost more than $100. You would enter the expression <=100 in the Validation Rule field (no $ sign is needed).

At the same time, you would enter text in the Validation Text propertyfor example, Unit price must be $100 or less. This text will appear in the error message (see Figure 5.11) whenever you enter, say, $100.50 or $160. If you don't enter any validation text, you get a general (and confusing) message stating that one or more values have been entered that are prohibited by the validation rule.

Figure 5.11. The purchase price for the first record has been edited to $124.50. An error message warns that the validation rule for the field has been broken.

If you enter, for example, $9.7g, it doesn't matter whether you have entered validation text. Access can't determine whether the validation rule has been broken because the g is invalid in a Currency field. You would just get a general message that the data is invalid.

The Validation Text property is in no way controlled by the Validation Rule property. You can enter an expression of <=100 in Validation Rule and Today is Mary's birthday in Validation Text. Access displays the text you entered whenever the validation rule is violated.

NOTE

Expressions and how to create them are important topics in Access. They are discussed more thoroughly in Chapter 7, "Finding and Filtering."

Required and Zero Length

Both null values and zero-length strings are "no something" entries, so it's worth considering the two properties together. (Both nulls and zero-length strings are discussed in Chapter 1.) Here's the question that the Required property answers: Is a null value a valid entry for this field? If you don't want null values, set the Required property to Yes because an actual value must then be entered. If you do want to allow them, set the property to No because a value will not be required.

The Allow Zero Length property can be set independent of the Required property and determines where a zero-length field will be allowed. Enter Yes to allow them and No to prohibit them.

Table 5.1 from Access Help summarizes how the two properties variously allow or prohibit null and zero-length fields:

Table 5.1. Effect of Combinations of Allow Zero Length and Required Properties on Value Stored

Allow Zero Length	Required	User's Action	Value Stored
No	No	Presses Enter	Null
		Presses spacebar	Null
		Enters a zero-length string	(not allowed)
		Presses Enter	Null
Yes	No	Presses spacebar	Null
		Enters a Zero-length string	zero-length string
		Presses Enter	(not allowed)
No	Yes	Presses spacebar	(not allowed)
		Enters a zero-length string	(not allowed)
		Presses Enter	(not allowed)
Yes	Yes	Presses spacebar	Zero-length string
		Enters a zero-length string	Zero-length string
Note: To enter a zero-length string, you type double quotation marks ("")

TIP

If you create a validation rule, null values aren't allowed. (If you want to allow null values when there is a validation rule, you must add the text string Or Is Null to the validation rule.) Using the Validation Rule property is a good way to both require a value and set limits on the values that can be entered.

Indexes

As with an index in a book, you can create indexes in Access to help it find values faster. This can be useful for speeding up queries, as well as sorting and grouping. On the downside, creating indexes can slow the updating of field values. When working with small databases, however, creating an index might not make that much of a difference, either positive or negative.

When you set a primary key for a single field, the Indexed property automatically becomes Yes (No Duplicates), for an index in which duplicates are not allowed. For fields that are not part of a primary key, you can also set the property to Yes (Duplicates Allowed). If you don't want an index, set this property to No.

You can also set multifield indexes. For example, suppose you have a database with thousands of customer records. Many of the records have Smith in the LastName field. Therefore, you might need to search on two fieldsLastName and FirstNameat the same time. A multiple-field index is helpful in speeding up these searches. Here are the steps:

1.	In Table Design view, choose View, Indexes.
2.	In the first blank row, type a name for the index in the Index Name column.
3.	Click in the Field Name column. Open the drop-down list and choose the first field for the index. In the previous example, the LastName field would be first.
4.	Click in the next row in the Field Name column, open the drop-down list, and choose the next field for the index. In the previous example, the FirstName field would be selected. You can leave the Index Name column blank.
5.	Continue adding fields as necessary, and then close the Indexes dialog box. You can add up to 10 fields for any one multiple-field index. If you need to insert a row, right-click in the row above which you want a blank row and choose Insert, Rows.

Figure 5.12 shows a multiple-field index for LastName and FirstName fields, along with the index for the table's primary key.

Figure 5.12. You use the Indexes dialog box to create multifield indexes.