Changing Data Attributes

As you learned in the previous chapter, Access 2007 provides a number of different data types. These data types help Access work more efficiently with your data and also provide a base level of data validation; for example, you can enter only numbers in a Number or Currency field.

When you initially design your database, you should match the data type and length of each field to its intended use. You might discover, however, that a field you thought would contain only numbers (such as a U.S. ZIP Code) must now contain some letters (perhaps because you’ve started doing business in Canada). You might find that one or more number fields need to hold larger values or a different number of decimal places. Access allows you to change the data type and length of many fields, even after you’ve entered data in them.

Changing Data Types

Changing the data type of a field in a table is simple. Open the table in Design view, click in the Data Type column of the field definition you want to change, click the arrow button at the right to see the available choices, and select a new data type. You cannot convert an OLE Object, an Attachment, or a ReplicationID data type to another data type. With several limitations, Access can successfully convert every other data type to any other data type, even when you have data in the table. Table 5–3 shows you the possible conversions and potential limitations when the table contains data.

Caution 

When the field contents don’t satisfy the limitations noted in Table 5–3, Access 2007 deletes the field contents (sets it to Null) when you save the changes.

Table 5–3: Limitations on Converting One Data Type to Another
Open table as spreadsheet

Convert To	From	Limitations
Text	Memo	Access truncates text longer than 255 characters.
	Hyperlink	Might lose some data if the hyperlink string is longer than 255 characters.
	Number, except ReplicationID	No limitations.
	AutoNumber	No limitations except ReplicationID.
	Currency	No limitations.
	Date/Time	No limitations.
	Yes/No	Yes (-1) converts to Yes; No (0) converts to No.
Memo	Text	No limitations.
	Hyperlink	No limitations.
	Number, except ReplicationID	No limitations.
	AutoNumber	No limitations.
Memo	Currency	No limitations.
	Date/Time	No limitations.
	Yes/No	Yes (-1) converts to Yes; No (0) converts to No.
Hyperlink	Text	If the text contains a valid hyperlink string consisting of a display name, a # delimiter, a valid link address, a # delimiter, and optional bookmark and ScreenTip, Access changes the data type without modifying the text. If the text contains only a valid link address, Access surrounds the address with # delimiters to form the hyperlink field. Access recognizes strings beginning with http://, ftp://, mailto:, news:, \\servername, and d:\ as link addresses. Access also assumes that a text string in the form text@text is an e-mail address, and it adds mailto: to the beginning of the string before converting it. If Access does not recognize the text as a link, it converts the text to [text]#http://[text]#, where [text] is the original contents of the field; the result is probably not a valid link address.
	Memo	Same restrictions as converting from Text
	Number, except ReplicationID	Possible, but Access converts the number to a text string in the form [number]#http://[number]#, where [number] is the text conversion of the original numeric value; the result is probably not a valid link address.
	AutoNumber	Possible, but Access converts the AutoNumber to a text string in the form [number]#http://[number], where [number] is the text conversion of the original AutoNumber; the result is probably not a valid link address.
	Currency	Possible, but Access converts the currency value to a text string in the form [currency]#http://[currency], where [currency] is the text conversion of the original currency value; the result is probably not a valid link address.
	Date/Time	Possible, but Access converts the date/time to a text string in the form [date/time]#http://[date/time]#, where [date/time] is the text conversion of the original date or time value; the result is probably not a valid link address.
	Yes/No	Possible, but Access converts the yes/no to a text string in the form [yes/no]#http://[yes/no], where [yes/no] is the text conversion of the original yes (−1) or no (0) value; the result is probably not a valid link address.
Number	Text	Text must contain only numbers and valid separators. The number value must be within the range for the Field Size property.
	Memo	Memo must contain only numbers and valid separators. The number value must be within the range for the Field Size property.
	Hyperlink	Not possible.
	Number (different field size or precision)	Number must not be larger or smaller than can be contained in the new field size. If you change precision, Access might round the number.
	AutoNumber	The number value must be within the range for the Field Size property.
	Currency	Number must not be larger or smaller than can be contained in the Field Size property.
	Date/Time	If the Field Size is Byte, the date must be between April 18, 1899, and September 11, 1900. If the new Field Size is Integer, the date must be between April 13, 1810, and September 16, 1989. For all other field sizes, there are no limitations.
	Yes/No	Yes (−1) converts to −1; No (0) converts to 0.
AutoNumber	Text	Not possible if the table contains data.
	Memo	Not possible if the table contains data.
	Hyperlink	Not possible.
	Number	Not possible if the table contains data.
	Currency	Not possible if the table contains data.
	Date/Time	Not possible if the table contains data.
	Yes/No	Not possible if the table contains data.
Currency	Text	Text must contain only numbers and valid separators.
	Memo	Memo must contain only numbers and valid separators.
	Hyperlink	Not possible.
	Number, except Replication ID	No limitations.
	AutoNumber	No limitations.
	Date/Time	No limitations, but value might be rounded.
	Yes/No	Yes (−1) converts to $1; No (0) converts to $0.
Date/Time	Text	Text must contain a recognizable date and/or time, such as 11-Nov-08 5:15 PM.
	Memo	Memo must contain a recognizable date and/or time, such as 11-Nov-08 5:15 PM.
	Hyperlink	Not possible.
	Number, except Replication ID	Number must be between −657,434 and 2,958,465.99998843.
	AutoNumber	Value must be less than 2,958,466 and greater than −657,433.
	Currency	Number must be between −$657,434 and $2,958,465.9999.
	Yes/No	Yes (−1) converts to 12/29/1899; No (0) converts to 12:00:00 AM.
Yes/No	Text	Text must contain only one of the following values: Yes, True, On, No, False, or Off.
	Memo	Text must contain only one of the following values: Yes, True, On, No, False, or Off.
	Hyperlink	Not possible.
	Number, except Replication ID	Zero or Null converts to No; any other value converts to Yes.
	AutoNumber	All values evaluate to Yes.
	Currency	Zero or Null converts to No; any other value converts to Yes.
	Date/Time	12:00:00 AM or Null converts to No; any other value converts to Yes.

If you want to see how this works in the Contacts table you have been building, open the table in Datasheet view and enter any last name and first name in one or two rows. We want to change the EmailName field from the Text data type that the table template provided to Hyperlink. Scroll right and enter an invalid e-mail address in one of the rows in the form: Proseware email address. In another row, add the correct URL prefix in the form: mailto:[e-mail].

Now, switch to Design view and change the data type of the EmailName field from Text to Hyperlink and save the change. Notice that Access 2007 gives you no warning about any conversion problems because it knows it can store any text field that is not larger than 255 characters in a hyperlink, which can be up to 8,192 bytes. Save this change to the table, switch back to Datasheet view, and scroll to the right to find the changed field. You should see a result something like Figure 5–21.

Figure 5–21: Access 2007 can convert the Text data type to Hyperlink, but will get it right only if the text contains a recognizable protocol string.

Both entries look fine. However, if you click on the first one, Access 2007 attempts to open your browser because the full text stored in the hyperlink is Proseware email address#http://Proseware email address#. Because the link address portion indicates the HTTP protocol, your browser attempts to open instead of your e-mail program. Access displays a message box that says it cannot follow the hyperlink. When you click on the second link, it should open a blank message in your e-mail program with the To: line filled in correctly. Access recognized the mailto: prefix and converted the text correctly.

You can read more about working with hyperlinks in Chapter 10, “Using Forms.” We show you how to make sure that Access correctly recognizes an e-mail name typed into a hyperlink field in Chapter 20, “Automating Your Application with Visual Basic.”

Changing Data Lengths

For text and number fields, you can define the maximum length of the data that can be stored in the field. Although a text field can be up to 255 characters long, you can restrict the length to as little as 1, character. If you don’t specify a length for text, Access 2007 normally assigns the length you specify in the Table Design section in the Object Designers category of the Access Options dialog box. (The default length is 255.) Access won’t let you enter text field data longer than the defined length. If you need more space in a text field, you can increase the length at any time; but if you try to redefine the length of a text field so that it’s shorter, you will get a warning message (like the one shown in Figure 5–22) stating that Access will truncate any data field that contains data longer than the new length when you try to save the changes to your table. Note also that it warns you that any validation rules you have designed might fail on the changed data.

Figure 5–22: This dialog box informs you of possible data truncation problems.

Inside Out-Setting Field Defaults Through Access Options 

Remember, you can change the default data type for a new field and the default length of new text and number fields by clicking the Microsoft Office Button, clicking Access Options, clicking the Object Designers category of the Access Options dialog box, and then selecting your defaults in the Table Design section.

If you want to try this in your Contacts table, open it in Design view, change the length of the MiddleInit field to 10, and save the change. Switch to Datasheet view and type more than one character in MiddleInit. Now switch back to Design view and set the length of MiddleInit to y. When you try to save the change, you should see the error message in Figure 5–22 (because you’re shortening the length of the MiddleInit field). Click Yes to allow the changes and then switch back to Datasheet view. You should find the data you typed truncated to one character in MiddleInit. Review Table 5–2 (page 215) and verify that each field’s length in your Contacts table matches tblContacts in the Conrad Systems Contacts database and make any necessary adjustments before proceeding further.

Sizes for numeric data types can vary from a single byte (which can contain a value from 0. through 255) to 2. or 4, bytes (for larger integers), 8) bytes (necessary to hold very large floating-point or currency numbers), or 16 bytes (to hold a unique ReplicationID or decimal number). Except for ReplicationID, you can change the size of a numeric data type at any time, but you might generate errors if you make the size smaller. Access also rounds numbers when converting from floating-point data types (Single or Double) to integer or currency values.

Dealing with Conversion Errors

When you try to save a modified table definition, Access 2007 always warns you if any changes to the data type or field length will cause conversion errors. For example, if you change the Field Size property of a Number field from Integer to Byte, Access warns you if any of the records contain a number larger than 255. (Access deletes the contents of any field it can’t convert at all.) If you examine Table 5–3, you’ll see that you should expect some data type changes to always cause problems. For example, if you change a field from Hyperlink to Date/Time, you can expect Access to delete all data. You’ll see a dialog box similar to the one shown in Figure 5–23 warning you about fields that Access will set to a Null value if you proceed with your changes. Click Yes to proceed with the changes. You’ll have to examine your data to correct any conversion errors.

Figure 5–23: This dialog box informs you of conversion errors.

If you click No, Access 2007 opens the dialog box shown in Figure 5–24. If you deleted any fields or indexes, added any fields, or renamed any fields, Access will save those changes. Otherwise, the database will be unchanged. You can correct any data type or field length changes you made, and then try to save the table definition again.

Figure 5–24: This dialog box appears if you decide not to save a modified table definition.

Changing Other Field Properties

As you learned in Chapter 4, you can set a number of other properties that define how Access 2007 displays or validates a field that have nothing to do with changing the data type. These properties include Description, Format, Input Mask, Caption, Default Value, Validation Rule, Validation Text, Required, Allow Zero Length, and Indexed.

If you have data in your table, changing some of these properties might elicit a warning from Access. If you change or define a validation rule, or set Required to Yes, Access offers to check the new rule or requirement that a field not be empty against the contents of the table when you try to save the change. If you ask Access to test the data, it checks all the rows in your table and opens a warning dialog box if it finds any rows that fail. However, it doesn’t tell you which rows failed-we’ll show you how to do that in Chapter 7. If you changed the rules for more than one field, you’ll see the error dialog box once for each rule that fails.

As you’ll learn later, when you define queries, forms, and reports, these objects inherit several of the properties that you define for your table fields. In previous versions of Access, the catch was that once you defined and saved another object that used table fields, any subsequent change that you made to properties in table design didn’t change automatically in other dependent objects. You had to go find those properties yourself and fix them, or use a tool such as the Speed Ferret product from Black Moshannon Systems (www.moshannon.com). You would get the new property settings in any new objects you created, but the old ones remained unchanged.

The good news is there’s a feature in Access 2007 that takes care of this problem for some properties. To see how this works, you must first make sure that you have this option selected in Access Options as we showed you in the previous chapter. Click the Microsoft Office Button, click the Access Options button, click the Object Designers category, and verify that you have selected the Show Property Update Options Buttons check box. Click OK to close the Access Options dialog box.

Next, open the Contacts table in Design view in the Contact Tracking database you have been building. Remember from the previous chapter that Access displays the description on the status bar when the focus is on the Description field in any datasheet or form. Click in the Description column next to the ContactID field and change the description from “Unique contact ID" to just “Contact ID" and then press Tab. As soon as you do this, you’ll see an AutoCorrect smart tag that looks like a lightning bolt. If you rest your mouse pointer near the smart tag, it tells you that it offers property update options. Click the arrow next to the tag to see the options you can choose from as shown in Figure 5–25. Access offers you these options whenever you change the Description, Format, or Input Mask properties.

Figure 5–25: When you change a field description, you see a smart tag offering property update options.

You can click Update Status Bar Text Everywhere ContactID Is Used to ask Access to also change this property wherever the ContactID field is used in other objects. Of course, you don’t have anything but tables in your sample database right now, so clicking this command won’t do anything. You can select Help On Propagating Field Properties to open the Help window to read how this works.

Caution 

You must click the Update Status Bar Text Everywhere ContactID Is Used command immediately after you make the change in your table definition. If you move to another field or move to another property and make another change, the smart tag disappears. You can make it reappear by returning to the property you changed and changing it again. If you choose to make changes, Access opens an Update Properties dialog box that lists all the objects it plans to change. You can reject all changes or selectively apply the change to only some of the objects.