Le Moyne College CSC-151 Microsoft Access 2003 Reference

Table design view - field properties

The following field properties, found in the table design view, are described on this page. Field Size Format Input Mask Caption Default Value Validation Rule Validation Text Required Allow Zero Length The bottom five field properties shown in the picture to the right are not discussed in this class.

Field Size

For the Text data type, see the first entry in the data types table.

For the Number data type, see the number field size property table.

None of other data types used by Microsoft Access has a Field Size property associated with it.

Format

For the Text and Memo data types, one of the following four symbols may be entered into this property.

@	Text character (either a character or a space) is required.
&	Text character is not required.
<	Force all characters to lowercase.
>	Force all characters to uppercase.

For the Date/Time data type, you must choose one of the following formats from the drop down list.

General Date	combines the Short Date and Long Time formats.
Long Date	Example: Saturday, April 3, 1993
Medium Date	Example: 3-Apr-93
Short Date	Example: 4/3/93
Long Time	Example: 5:34:23 PM
Medium Time	Example: 5:34 PM
Short Time	Example: 17:34

For the Number and Currency data types, you must choose one fo the following formats from the drop down list.

General Number	Display the number as entered.
Currency	Display the currency symbol, thousand separator, and decimal places.
Euro	Display the euro currency symbol.
Fixed	Display at least one digit and decimal places.
Standard	Display the thousand separator and decimal places.
Percent	Multiply the value by 100 and append a percent sign (%).
Scientific	Use standard scientific notation.

Input Mask

The Input Mask field property is used to display a field value based on a predefined format (mask). Note that the Input Mask only affects how the data is displayed. That is, the data stored in the field does not include the special mask (formatting) specified in this property.

When you put the cursor in the Input Mask property textbox, the elipses button appears to the right of the textbox. Clicking on this button displays the Input Mask Wizard, as shown on the right. The Input Mask Wizard includes ten pre-defined input masks - starting with Phone Number and ending with Medium Date. To add an input mask for the Home Phone Number field in the Student table, we would: Position the cursor on the Home Phone Number field in the field list. Click on the Input Mask property textbox. Click on the elipses button to the right of the Input Mask textbox. Select Phone Number from the list of pre-defined input masks. Click on the Next button.
The picture on the right is now displayed. Since a pre-defined input mask was selected, there is nothing to do on this step of the wizard. Click on the Next button.
The picture on the right is now displayed. As you can see, there are two choices at this point in the wizard. The first choice - "With the symbols in the mask" - means that the special symbols used in the mask will be stored as part of the data value for this field. This means that the Field Size value for this Text field must include enough space for each of the special symbols used in the mask. In the case of the Phone Number mask, this includes (, ), space, and dash (-). The second (deafult) choice - "Without the symbols in the mask" - means that the special symbols used in the mask are only used when dislaying a data value. This is what we want, so no change is necessary. Click on the Next button.
The picture on the right is now displayed. Click on the Finish button. At this point, the cursor is in the Input Mask property for the Home Phone Number field. The value in this Input Mask property will look like: After you move the cursor off of the Input Mask property, the value in the Input Mask property will look like:
For this class, it is not inportant for you to understand the significance of the various symbols used in an Input Mask property value. In a similar fashion, we can create an Input Mask property value for the Zip Code field contained in the Student table.

Caption

The Caption property for a field is used in two ways:

1. When creating a Form object, it specifies the text for labels attached to controls created by dragging a field from the field list.
2. In a Datasheet view, it serves as the column heading for the field.

When the Caption property is left blank, the field name is used in the two ways described above.

Default Value

Specifies a value that is automatically entered in a field when a new data row (record) is created.

For example, we may want to have New York be the default for the State field in the Student table. We put the cursor in the State row of the field list, and then type in ny in the Default property. When we move the cursor off of the Default property textbox, the value we entered changes to "ny" . The qoutes are inserted by Access to indicate that the Field is a Text data type.

Validation Rule

Specify an expression that limits the values that can be entered in the field.

For example, let's assume that Students will only live in New York, Pennsylvania, or New Jersey. We would enter a validation rule of: ny or pa or nj . When we move the cursor off of the Validation Rule property textbox, the value we entered changes to "ny" Or "pa" Or "nj" . The qoutes are inserted by Access to indicate that the Field is a Text data type.

Thus, you use the Validation Rule property to specify requirements for data entered into a field. When data is entered that violates the Validation Rule expression, you can use the Validation Text property to specify the message to be displayed to the user.

Validation Text

The error message that appears when you enter a value prohibited by the Validation Rule.

Continuing the Validation Rule example, we might enter the following into the Validation Text property: The State must be ny, pa, or nj.

Thus, the Validation Rule and Validation Text properties are used to validate data entered by a user and to report an error when the data entered is not valid.

If you have a Validation Rule but do not enter a Validation Text value, Microsoft Access will generate an error message to display to the user.

Required

The Required property is used to specify whether a value is required in a field. You must select either Yes or No from the drop down list.

Allow Zero Length

Indicating Yes for the Allow Zero Length property indicates that a zero-length string is a valid entry in a table field.