Restricting the Amount of Data in a Field

The Field Size property, which is valid for the Text, Number, and AutoNumber data types, restricts the number of characters that can be entered in a text field (from 0 to 255) and the number of digits that can be entered in a number or AutoNumber field. You can set number fields to any of the following values:

Open table as spreadsheet

Setting	Description
Byte	Stores whole numbers from 0 to 255
Integer	Stores whole numbers from -32,768 to 32,767
Long Integer	(The default.) Stores whole numbers from -2,147,483,648 to 2,147,483,647
Single	Stores negative numbers from -3.402823E38 to -1.401298E-45 and positive numbers from 1.401298E-45 to 3.402823E38
Double	Stores negative numbers from -1.79769313486231E308 to -4.94065645841247E-324 and positive numbers from 1.79769313486231E308 to 4.94065645841247E-324
Decimal	Stores numbers from -10^28 -1 through 10^28 -1

AutoNumber fields are automatically set to Long Integer.

By setting the Field Size property to a value that allows the largest valid entry, you prevent the user from entering certain types of invalid information. If you try to type more characters in a text field than the number allowed by the Field Size setting, an audio alert sounds, and Access refuses to accept the entry. Likewise, Access rejects any value that is below or above the limits of a number field when you try to move out of the field.

In this exercise, you will change the Field Size property for several fields to see the impact this has on data already in the table and on new data that you enter.

Use the 02_Size database. This practice file is located in the Chapter12 subfolder under SBS_Office2007.

Open the 02_Size database. Then open the Field Property Test table in Datasheet view.

1. Review the contents of the one record.

2. On the View toolbar, click the Design View button.

3. Click any cell in the fText row, and then in the Field Properties area, change the Field Size property from 255 to 12.

4. Click any cell in the fNumber row, click the Field Size arrow, and then in the list, click Byte.

Access restricts the number of characters that can be entered in the text field to 12, and the values that can be entered in the number field to the range from 0 to 255 (inclusive).

5. Switch to Datasheet view, clicking Yes when prompted to save the table.

The table contains data that doesn’t fit these new property settings, so Access displays a warning that some data might be lost.

6. Click Yes to acknowledge the risk, and click Yes again to accept the deletion of the contents of one field.

fText now contains only 12 characters, rather than the 32 you entered. The other 20 characters have been permanently deleted. fNumber is empty because it is now limited to whole numbers from 0 through 255, and the value of 500 that you entered was deleted.

7. In the fNumber field, type 2.5, and then press the key.

Access rounds the entered value to the nearest whole number.

Close the Field Property Test table and the 02_Size database.