Setting Field Properties

Although only nine data types are available in Access, each data type can be customized in many ways. This customization is done through properties . This is the first time we've formally discussed properties in this book, so we'll explore the concept a bit further.

Saying that a data field is a text field, number field, or hyperlink field is a bit like saying that a piece of food is a fruit, vegetable, or meat. When you know that there's a fruit on the menu, you know something about the mealbut there are still many possibilities. The fruit might be a grape, watermelon, tangerine, or nectarine. If I tell you the fruit is red, you can narrow the possibilities a bit. If you know it's red, sweet, juicy, and about the size of a marble, you might properly guess that the fruit is a cherry.

The color, flavor, texture, and size of the fruit are its propertiesthe things that describe that particular fruit and distinguish it from all other fruits. In addition, each property has a particular value: The color is red, the flavor is sweet, and so on. Moving back to Access, fields have properties, too. A text field, for example, has properties such as field size, input mask, caption, and default value. If you know the values of those properties, you know a lot more about the particular field.

Of course, properties have uses beyond simply distinguishing one field from another. The field size property, for example, dictates how much data you can store in a field. So, you can use the properties to help limit your database to storing reasonable data, as well as for other purposes.

In the next section of this chapter, we'll introduce you to some of the most important properties of the various data types.

Text Field Properties

To explore field properties, you'll build a new table named Plantings, which will track information about exactly which seeds you planted in the garden and when. You'll build this table entirely in design view. To get started with a single field in the table, follow these steps:

  1. Launch Access and load the Plants database.

  2. Select the Tables shortcut in the Database window.

  3. Double-click the Create Table in Design View shortcut.

  4. Enter Bed as the field name in the first row of the design grid, and select Text as the data type. Figure 11.2 shows the new table at this point.

    Figure 11.2. The table in design view.

    graphics/11fig02.gif

Take a moment to examine the design view of this table. You already know about field names and data types. To the right of the data type is a space for the field description; type Bed where the planting is located in this area for the Bed field. The description is one of the properties of the field. Beneath the grid of field names, data types, and descriptions is another area for properties, divided into two tabs. For this chapter, you'll work with the General tab; the properties on the Lookup tab are used by the Lookup Wizard, and you usually won't modify them directly. (You can get a brief look at the Lookup properties in Chapter 6.)

Each data type has its own set of properties on the General tab. The properties you're seeing now are all the properties that describe a text field. Some of these are advanced properties you won't need to worry aboutfor example, you probably won't need to change the Unicode Compression or IME Mode property. As you select different properties in the list, you'll notice that the area to the right of the properties list changes to show you a short bit of help on the current property. Of course, you can also click F1 at any time if you need more help.

Here's a list of the text field properties you might need to work with as you're learning:

  • Field size This property dictates the maximum number of characters you can store in the field. You can have fewer characters in the field, but Access won't let you enter more than this number. As mentioned previously, the maximum field size for a text field is 255.

  • Format The format property controls the way in which the data in a field is displayed. You'll learn more about this when we discuss date/time fields in the section "Date/Time Field Properties."

  • Input mask The input mask property supplies a pattern that data entered into the field must match. We'll show you an input mask when we discuss date/time fields.

  • Caption The caption property is used in place of the field name when the field name is displayed. For example, the columns in a datasheet will be labeled by captions instead of field names if you supply a caption. This enables you to use friendlier names where people will see them, while still using names without spaces for the fields themselves .

  • Default value The default value property supplies a value to be used for the field in a new record. If you don't overwrite the default value with other data, it is saved with the record.

  • Validation rule The validation rule property lets you specify a formula that limits the data entered into a field. You'll see an example when we discuss the number data type in the section "Number Field Properties."

  • Validation text The validation text property supplies a message that Access displays if the user tries to break a validation rule.

  • Required The required property is just what it sounds like: If you set it to Yes, you can't save a record in the table without filling in this field.

To continue working with the Plantings table, set some properties of the Bed field by doing the following:

  1. Enter 30 as the Field Size property.

  2. Set the Caption property to Garden Bed .

  3. Set the Required property to Yes .

  4. Set the Bed field as the primary key for the new table by selecting it in the list of fields and clicking the Primary Key toolbar button.

  5. Select File, Save and save the new table with the name Plantings . Figure 11.3 shows the saved table, still in design view.

    Figure 11.3. The table in design view with properties set.

    graphics/11fig03.gif

Now switch the Plantings table from design view to datasheet view using the View button on the toolbar. Drag the mouse to make the column a bit wider, and enter some data, as shown in Figure 11.4. You can see that some of the properties you set for the field make a difference in the user interface.

Figure 11.4. Entering data in a field with a caption and description.

graphics/11fig04.gif

You'll also find other effects of the properties that you can't immediately see. If you try to enter more than 30 characters in the Bed field, for example, Access refuses to accept the extra characters. The field size property sets an absolute limit here.

The effects of property settings go beyond the table itself because properties in Access are inherited whenever such inheritance makes sense. To see how this works, try following these steps:

  1. Close the Plantings table.

  2. Select the Plantings table in the Database window.

  3. Click the New Object: AutoForm button on the toolbar.

Figure 11.5 shows the AutoForm's results. Access has reused the Caption and Description properties of the field on the form. The Status bar displays the Description property's text. Often, a quick glance at the Status bar during data entry can answer questions about the type of data you're entering. Inheritance applies to queries, reports , and pages as well as forms. If you'd like, save the form as Plantings, and then close it.

Figure 11.5. A form including a field with a caption and description.

graphics/11fig05.gif

Tip

graphics/tman.gif

To change the value of a property such as Required from No to Yes, or vice versa, just double-click the property. If you don't want to use the mouse, enter just the first letter or two of the setting, and Access will fill in the rest for you.


Memo Field Properties

To continue working with field properties, you'll add a memo field to the Plantings table by following these steps:

  1. Open the Plantings table in design view.

  2. Enter a new field named Notes . Set its data type to Memo . You'll see that the properties section of the design view changes to show a slightly different set of properties for a memo field than for a text field.

  3. Set the Description of the new field to Notes about the planting .

  4. Set the Caption of the new field to Planting Notes .

If you compare the property list for a memo field with the property list for a text field, you'll see that they are almost identical. That makes sense because a memo field is just a big text field.

Number Field Properties

With a numeric field, you'll see some new properties and some new twists on some old ones. Here's how to add a numeric field to the Plantings table:

  1. Add a new field named NumberPlanted . Set its data type to Number.

  2. Set the Description of the new field to Number of plants planted .

  3. Click in the Field Size property to reveal the drop-down arrow to the right of the property. Then click the drop-down arrow to see the choices for numeric field sizes, as shown in Figure 11.6. Select the Byte field size for this field.

    Figure 11.6. Selecting a field size for a numeric field.

    graphics/11fig06.gif

  4. Set the Decimal Places property to .

  5. Set the Caption property to Number Planted .

  6. Set the Default Value property to 1 .

  7. Set the Validation Rule property to <=50 .

  8. Set the Validation Text property to You can't plant more than 50 at one time .

  9. Set the Required property to Yes .

Similar to the field size property of a text field, the field size property of a number field imposes a limit on the amount of data you can enter in the field. But unlike the field size for a text field, the field size for a number field is the name of a type of number. Table 11.1 shows you how to translate these names into the actual ranges the field can store.

Table 11.1. Field Sizes for Numeric Fields

Name

Minimum Value

Maximum Value

Exact?

Maximum Decimal Places

Byte

255

Yes

Integer

32,768

32,767

Yes

Long Integer

2,147,483,648

2,147,483,647

Yes

Single

3.4 x 10 38

3.4 x 10 38

No

7

Double

1.8 x 10 308

1.8 x 10 308

No

15

Replication ID

n/a

n/a

n/a

n/a

Decimal

1 x 10 38

1 x 10 38

Yes

28

When choosing a field size for a numeric data type, you need to keep in mind several things:

  • What's the largest value you might need to store?

  • Is the value a whole number, or does it have a fractional part?

  • Are you worried about rounding errors?

Whole numbers are best stored in byte, integer, or long integer fields. Choose the smallest type that will actually hold the data you're expecting because the smaller types are faster.

Note

graphics/nman.gif

Replication IDs are coded values that are most useful in databases that are maintained on multiple computers simultaneously . You'll probably never use a replication ID in your own database.


Numbers with fractions can be stored in single, double, or decimal fields. Again, you can choose based on how large the data is, but the other factor to keep in mind is rounding. Single and double fields might return a number slightly different from the one you typed if the original number had many decimal places. The decimal data type stores the decimal places exactlybut it's slower to process than single or double.

We've also included a validation rule for the NumberPlanted field. In English, you might express this rule as "the number entered must be less than or equal to 50." If this is a true statement, Access will let you enter the number. If it's not true, Access will display the validation text instead. Here's how you can see this in action:

  1. Save the Plantings table. When you do this, you'll see the somewhat scary warning shown in Figure 11.7. That's because Access wants to know whether to apply the new validation rule to the existing rows in the table. The table doesn't have any rows, so you can just click No here.

    Figure 11.7. Data validation warning.

    graphics/11fig07.jpg

  2. Switch to datasheet view. Notice that the NumberPlanted field already has the value 1 in the new record row, as specified by its default value property. If you entered a record earlier, you might notice that Access doesn't enter the default value of 1 for the existing record. That's because you set the Default Value property after entering that record, and the Default Value only applies to new records.

  3. Enter some data in the new record, including a value of 52 for the number planted. When you attempt to leave the field, you'll get the error message shown in Figure 11.8. Until you correct the validation error, you can't leave the field or save the record.

    Figure 11.8. Attempting to violate a validation rule.

    graphics/11fig08.gif

  4. Click OK and change the value in the Number Planted field to 48 . Now you'll be able to tab out of the field and save the record.

Access supports a complex syntax that allows you to build a wide variety of validation rules. Fortunately, you don't need to learn the entire syntax to get started! Table 11.2 shows you examples of some common validation rules.

Table 11.2. Examples of Validation Rules

Rule

Meaning

<=50

Less than or equal to 50.

<>5

Not equal to 5any value except 5 is allowed.

Is Not Null

Must enter something, rather than leaving the field blank. This is equivalent to setting the required property to Yes.

<10 or Is Null

Less than 10; otherwise , blank.

Like "B*"

Must start with the letter B . The asterisk is a wild card that matches any number of characters.

Like "?????X"

Must be six characters and end with the letter X . The question mark is a wild card that matches precisely one character.

Between #1/1/2004# And #12/31/2008#

Any date between January 1, 2004, and December 31, 2008. The pound signs are how Access indicates dates in an expression.

Tip

graphics/tman.gif

For most data, you'll be safe using long integer fields for numbers without fractions and double fields for numbers with fractions.


Tip

graphics/tman.gif

Whenever you set the Validation Rule property, you should also set the Validation Text property. That's the best way to give feedback to database users when they make a mistake.


Date/Time Field Properties

The date/time data type gives you a chance to practice using the input mask and format properties. Here's how:

  1. Switch back to design view of the table.

  2. Create a new field named DatePlanted and set its data type to Date/Time . You'll notice that the date/time data type doesn't have a field size property. All dates and times are stored the same way by Access.

  3. Set the Description of the new field to Date that the plants were planted . When you enter the new description, Access will display the Property Update Options icon (a type of smart tag) next to the description. If you click the icon, you'll get two choices, "Update Status Bar Text everywhere DatePlanted is used" And "Help on propagating field properties." Because there's a form based on this table, Access monitors the table for property changes that might affect the form. DatePlanted is a new field, so there's no need to continue with the updates. These icons will continue to pop up as you work with the table, as long as you have a form based on the table.

  4. Click in the Format property to reveal the drop-down arrow to the right of the property. Click the drop-down arrow to see the choices for a list of formats, as shown in Figure 11.9. Select the Short Date format for this field.

    Figure 11.9. Selecting a format for a date/time field.

    graphics/11fig09.gif

  5. Click in the Input Mask property. A little button with three dots, called a builder button , will appear to the right of this property; click it. Access will prompt you to save the table; click Yes . Then the Input Mask Wizard will open.

  6. Select the Short Date input mask. You can try it by typing in the indicated area, as shown in Figure 11.10. You'll discover that the input mask prevents you from typing characters that don't fit into the short date format.

    Figure 11.10. Testing an input mask.

    graphics/11fig10.gif

  7. Click Finish to save the input mask to the table. You'll see that Access converts your choice in the wizard into a code in the property.

  8. Set the Caption property to Date Planted.

The format and input mask properties complement each other. The input mask controls what you can type into a field, and the format property controls how the results are displayed. Although you'll often use the same standard for both properties (as in this example), that's not required. You could use a short date input mask and a long date format, for example.

To see how the properties work, switch the table back to datasheet view and enter some dates in the DatePlanted field. You'll see that the input mask characters are displayed when the cursor is in that field, as shown in Figure 11.11.

Figure 11.11. An input mask in a datasheet.

graphics/11fig11.gif

Currency Field Properties

You won't find any surprises when you create a currency field. By now, you should find the properties of this field type familiar. But add one to the table anyhow, just for practice, by doing the following:

  1. Switch back to design view of the table.

  2. Create a new field named PurchasePrice and set its data type to Currency . You'll see that this automatically sets the format property of the field to Currency, which displays the proper currency symbol for your locality.

  3. Set the Description of the new field to Cost of the plants .

  4. Set the Caption property to Purchase Price .

AutoNumber Field Properties

You might recall from Chapter 5 that autonumber fields are used when you want Access to create primary key values for your table. So far in this table, you really don't have a good candidate for a natural primary key. So, follow these steps to add an autonumber primary key:

  1. Add another field to the list in table design view, and name it PlantingKey .

  2. Set the data type of the new field to AutoNumber.

  3. Set the description to Unique key for this record (generated by Access) .

  4. Right-click the field name and select Primary Key to make the new field the primary key of the table replacing the current primary key.

  5. Save the table.

  6. Switch to datasheet view. You'll find that Access has assigned a unique value to the PlantingKey field in each existing record, as shown in Figure 11.12. If you add a new row, it too will be assigned a unique primary key.

    Figure 11.12. An autonumber field is assigned by Access.

    graphics/11fig12.gif

In most cases, you won't need to change the properties of an autonumber field. You might want to add a caption or description to aid in identifying the field in datasheets and on forms and reports. The field size property lets you choose between Long Integer and Replication ID, whereas the new value's property lets you choose between Increment and Random. You should generally leave these set to their defaultsLong Integer and Increment.

Tip

graphics/tman.gif

The format Access uses for currency is taken straight from Windows. You can modify this in the Regional Settings application in Control Panel if you want.


Tip

graphics/tman.gif

Access doesn't let you enter (or change) a value in an autonumber field yourself. If you try typing in an autonumber field, Access just beeps at you.


Yes/No Field Properties

You've already seen all the properties of Yes/No fields on other fields. This data type displays a small check box, which you'll select during data entry to indicate a yes value. Leaving the check box unselected (or unchecked) indicates a no value. Here's an example:

  1. Switch back to table design view. Add another field to the list in table design view and name it Future .

  2. Set the data type of the new field to Yes/No.

  3. Set the description to Plant this variety again in the future?

  4. Set the caption to Future Planting .

  5. Set the default value to Yes .

  6. Save the table.

  7. Switch to datasheet view. Access automatically displays the yes/no field as a check box, which is checked for yes and unchecked for no. Again, notice that Access doesn't check Yes, as the default value for existing records. Only the new record row's field is checked Yes.

If you investigate the choices for the format property of a yes/no field, you'll see that such fields are also suited to represent on/off or true/false choices.

Tip

graphics/tman.gif

The default value of a newly-created yes/no field is no. If most of your records will require a yes value, you can change the field's Default Value property to yes in design view. Then, simply uncheck the fields as the no values occur. This way, you can save yourself a few keystrokes.


OLE Object Field Properties

We just have a few more field types to look at! From the properties point of view, OLE object fields are very simple. Here's an example that shows you how to set the properties for an OLE object field:

  1. Switch back to table design view. Add another field to the list in table design view and name it Photo .

  2. Set the data type of the new field to OLE Object.

  3. Set the description to Picture of the planting .

  4. Set the caption to Photo .

  5. Save the table.

The only properties for an OLE object field are the caption and required properties because these fields don't really store data Access needs to describe. Instead, they store data from outside Access. You can think of an OLE object field as a box into which you can put files saved by other programs. Access only knows which label to paste on the box and whether to insist that you put something inside it.

Hyperlink Field Properties

Finally, Access includes support for a special field type, the hyperlink. A hyperlink is similar to a text field, except that it's designed to work as a live link to online material. Here's how you can add a hyperlink field to the table you've been working with:

  1. Add another field to the list in table design view, and name it OnlineReference .

  2. Set the data type of the new field to Hyperlink.

  3. Set the description to Online reference material for this plant .

  4. Set the caption to More Info . You'll see that the hyperlink data type has most of the properties of the text data type. Figure 11.13 shows the table design with the full variety of data types included.

    Figure 11.13. A table with a variety of data types.

    graphics/11fig13.gif

  5. Save the table.

  6. Switch to datasheet view; then enter a URL (an Internet address, such as http://www.quepublishing.com/) in the new hyperlink field.

  7. Click the data in the hyperlink field to open the linked Web site in a separate browser window, as shown in Figure 11.14.

    Figure 11.14. Clicking a hyperlink field opens a browser window.

    graphics/11fig14.jpg

Tip

graphics/tman.gif

To edit the data in a hyperlink field, click in the previous field in the table and then press the Tab key to move to the hyperlink field.




Absolute Beginner's Guide to Microsoft Office Access 2003
Absolute Beginners Guide to Microsoft Office Access 2003
ISBN: 0789729407
EAN: 2147483647
Year: 2002
Pages: 124

Similar book on Amazon

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