Pin Me

Microsoft Access: Understanding Field Properties

written by: •edited by: Bill Fulks•updated: 10/15/2008

Each field in a Microsoft Access table has a set of properties which vary depending on the data type used, and each of these properties can be changed to determine how Access handles the data. Here we review the importance of field properties and their critical importance to database design.

  • slide 1 of 5

    Understanding Field Properties

    Field properties are an important and powerful part of any database design build. The integrity of your data hinges on setting field properties to the correct value. For example you may wish to display a full data item or just an abbreviation, you may set currencies to display with a specific number of decimal places, or a date and time stamp to appear in 8 or 10 character format. When you create fields Access allows you to display a list of properties based on the data type of that particular field. These field properties are different depending on the data type item chosen.

    The following data type items are related to the corresponding Field Type properties.

  • slide 2 of 5

    Text and Memo data type field properties

    Field Size - Used to vary field lengths, defaults to 50 characters.

    Format - Allows you to create custom formats for your text string, i.e. '@' = text required, '&' = text is not require, '<' = forced lower case, '>' = forced upper case.

    Input Masks - Used to ensure that all data entered into a field was the correct data entry pattern. Forces the user to enter values in an exact format.

    Caption - A naming convention given to a field to help the end user input the relevant details.

    Default Value - A self explanatory field type, automatically populating the field with a pre-defined default string.

    Validation Rule - Allows you to set limits to values to maintain data integrity.

    Validation Text - Used in conjunction with the Validation Rule will display a warning message if the user has not met certain validation rule criteria when populating the field.

    Required - Simply a value that denotes whether a field should be populated, i.e. is or is not mandatory.

    Allow Zero Length - Allows you to control the way that blank fields are handled.

    Indexed - The crucial field property for expedient queries, sorting and field manipulation. Indexed fields hold all tables and relationships together.

  • slide 3 of 5

    Number data type field properties

    Field Size - Allows you to limit the range of digits allowed within the field. The default setting being Long Integer and for decimal places set this should be set to Single or Double.

    Format - Enables numeric and date/time fields to display the contents of a field in a specific format.

    Decimal Places - Simply enough, sets the number and currency field to a chosen set of decimal places.

    Yes/No - Can contain one option from two choices. Yes/No, True/False, On/Off.

  • slide 4 of 5

    In the following part we will discuss the importance of Lookup Fields and how they can be used by multiple tables to populate highly valuable data, quickly via a simple set of masterfiles (Lookup Tables).