When you make a table in Access, you create fields to contain your data. Each of these fields has specific characteristics, which are formally known as properties.

A few field properties are primarily cosmetic in nature. For example, you can use the Format property to display a date as June 3, 1999, as opposed to 6/3/99. Other field properties, however, represent powerful tools for maintaining data integrity. One example is the Validation Rule property, which you can use to restrict the range of values that can be entered in a field. For instance, you could limit the values in the Quantity field of a Purchases table to numbers below 100. With that rule in place, you won’t be getting any deliveries of 67,000 pencil sharpeners.

Fields of different data types have different properties. A Number field has a property that controls the number of decimal places; a Text field does not. Moreover, depending on the data type, the same property will offer different choices. For example, the Format property for a Date/Time field will let you display 6/3/99 as June 3, 1999, whereas the Format property of a Number field lets you show 7898 as 7,898.

Whenever you create a field, Access assigns default field properties. You can leave all of these choices as they are or modify them as you see fit. Some properties, however, are easier to change than others. For instance, you can usually change the way a date displays without a hassle, but adding a new Validation Rule after you’ve entered a thousand records can be a headache because some of the existing data may violate the new rule. Therefore, it’s better to set field properties when you create a table or add new fields.

Let’s use the Northwind sample database to take a closer look at how field properties work.

 The Field Properties Pane.

Navigate to the Northwind.mdb database in the Samples subfolder of your Microsoft Office folder. Copy Northwind.mdb to the Clipboard and paste it in another folder. Rename the file CopyOfNorthwind.mdb, which you can now open and use without worrying about changing the original file. If you prefer to work with the original Northwind database, launch Access and click Help, Sample Databases, and Northwind Sample Database.

Double-click the Products table in the Database window to open it. Briefly review the fields and data in the table. Click View to switch to Design view. The cursor should be in the ProductID field, which has the AutoNumber data type. In the lower pane are field properties for this specific field. For example, the Field Size is set to Long Integer (a whole number), which is the correct choice for numerical IDs that increase one by one.

Press the Down arrow key to move to the ProductName field, which has a Text data type. Note that there is a different set of properties in the Field Properties pane. As with ProductID, there is a Field Size property, but here the setting refers to the maximum number of alphanumeric characters that can be entered in the field.

Click the Field Size property for the ProductName field. The description on the right side of the pane is a helpful reminder of how this specific property works.

The current setting for Field Size is 40. Type 10to modify the number and click Save. Access warns you that some data may be lost because product names with more than 10 characters may have already been entered (as is indeed the case). Click No, change the setting back to 40, and save your changes.

 Using Field Properties.

To get a better idea of how field properties work, let’s see a few more of them in action.

Required.This property controls whether you have to enter a value for the field. The Required property for the ProductName field is set to Yes. Click View. In the first record, delete Chai and press TAB. An error message appears that says you cannot have a Null value (nothing) in this field because the Required property is set to True (Yes). Click OK. Next, click Edit and UndoTyping to restore the Chai value.

Click View, and then click the UnitsInStock field. In the Field Properties pane, notice that the Validation Rule is set to >=0. With that expression, Access will only let you enter numbers greater than or equal to zero.

Note the Validation Text below. It is the same text you saw in the error message. You can edit this property to whatever you want to display in the error message. So even though Validation Rule and Validation Text are obviously related, the Validation Rule doesn’t control the entry in the Validation Text property.

Default Value.This is the value automatically entered in a particular field when you create a new record. Note that the Default Value for the UnitsInStock field is 0. Click View and use the horizontal scrollbar to put the UnitsInStock field in view. Scroll down to the last row, which is ready to accept a new record. When you add a new record, you can edit this field or leave it as its Default Value, which is 0 in this case.

Now that we’ve finished reviewing how field properties work, you can close the Products table and exit Access.