Lesson 4	Changing the data type
Objective	Edit the data type for a field.

MS Access Changing Column Data Type

Usually the data type for any particular field will be clear to you. For instance, a name is obviously a text field. And a date is obviously a date/time field. There are a few exceptions, however. The most notable exception is zip codes. Zip codes seem like they should be a number field, however, they really work better as a text field. That’s because some zip codes start with a zero, and if you enter them in a number field, Access drops the first zero. Other exceptions are numbers that you type with other characters, such as the parentheses and dash in a phone number, or the dashes in social security numbers. However, you can use something called an input mask to enter these types of data in number fields. But we’ll get to that later. For now, how about getting some hands-on experience with Table Design view? This simulation lets you display a table in Design view and change the data type of a field.

The Design view

This table contains client names and addresses. The last field contains the zip code. When the zip code data was entered Access identified the data type as Number, thus the leading zero of all of these zip codes was lost. To make Access store the leading zero, we need to change the data type of the Zip field from Number to Text. We’ll do this in Design view. There are several ways to open a table in Design view, however I usually find that I’m already looking at the datasheet when I need Design view. To display Design view when you are looking at Datasheet view, click the View button, the first button on the toolbar.
The top pane of the table design lists each field in the table and its data type. Zip, the last field shown, is the field we need to work with. Click the Data Type column for the Zip fieldthis will display the arrow for the drop-down list of data types. In order to change the data type for the field, you need to select from the drop-down list of options. To change the data type of the Zip field to a text field, click the down arrow and choose Text from the drop-down list.
The Zip field is now a text field. Now go back to the datasheet to see if the change made any difference. Click the View button, the first button on the toolbar, to display the datasheet. Notice that the View button has a different look when it displays the datasheet than it does when it displays the Design view.
Because you have changed the table design you must save the table before you can view the datasheet. Click Yes to save the datasheet.
The datasheet does not look different, however now when a leading zero is entered in the Zip field, it will be stored. The zip codes already entered will need to be updated by hand by entering the leading value for each zip code.

One of the benefits of data types is that they help prevent data errors. Another way of preventing errors is to use methods for

Controlling Data Entry

In this lesson you will learn one simple way to limit data entry, defining a data type for a field will prevent the entry of other data types. For instance, if you define a field as a number field, a user will not be able to enter text into that field.
While using the appropriate data type can be useful in preventing incorrect data entry, it doesn’t always prevent data entry error. Access provides two other features to test if data meets certain requirement to improve the quality of data entered--these features are input masks and validation rules. In addition, you may find drop-down lists in tables useful for accurate data entry. You will find the options for validation rules and input masks in the Field Properties pane (the bottom pane) of the Table Design view.
Another way to limit the information that can be entered in a field is to use a validation rule. Validation rules give you more flexibility in limiting the data that can be entered. For instance, you can specify that any entry must be larger or smaller than a certain value. Validation rules are used in conjunction with the validation text property, which contains the text displayed when entry into the field does not meet the validation rule. Validation rules are not covered in this course, but if you need them, you may want to experiment.

Add and remove fields from your table.

Data Type Usage - Exercise

The following exercise tests your understanding of data types.
Data Type Usage - Exercise