According to the Oxford dictionary a field is “an area of open land, especially one planted with crops or pasture, typically bounded by hedges or fences.” A farmer may have many fields; some that grow cotton, others that grow corn, or wheat or potatoes. Some, as shown by Kevin Costner’s character, even grow a field of dreams.
A field in a database is like a column in a spreadsheet. Yet, even though it is computer based, it can be likened to a farmer’s field.
Properties
A database field is defined by its properties. Properties are like the hedges and fences. They control the size of the field and the type of data you can plant in it. Properties also control the name and display of the field’s data. Each field may have many properties. Below is a description of the Name, Caption, Description, Data type, and Size properties of an Access database field.
Name property
Each field must have a name. The name can be up to 64 characters long. It may be composed of letters, numbers, and special characters like a dollar sign or pound sign. As shown below, some special characters are not allowed in a field name.
Special characters that can not be used in a field name
[table id=”5″]The field name should be descriptive – it should describe the purpose of the field. For example, a phone number field should be named “Phone”, and a notes field should be named “Notes”.
Some special characters, like a space, should be avoided. Although they are allowed, they can cause problems when the field is used in a calculation. To get around this, fields with spaces must be surrounded by square brackets. Consider the following examples:[table id=”6″]UpperCamelCase, also known as PascalCase, makes it easier to read a multi-word field name. This is a naming convention, in which the space has been removed and the first letter of each word has been capitalized, as in the fourth example above. Many developers argue that using PascalCase is easier than having to enter the square brackets when the field name is used in a calculation.
Caption property
To make a field appear more user friendly, Microsoft Access fields have a caption property. This property is optional. It defines the default label that appears next to a field in a form or report. It also provides the column heading for the field when viewing the data in a table or query. Unlike the name property, captions should have spaces and other characters as needed to make the label user friendly.
Description property
The description property explains the purpose of the field. It is optional. The chosen description will appear in the status bar when the field has the focus when viewing data in a table, query, or form. This helps the end user know what they can/should enter in to the field.
A good way to help end users is to form the description as a question. For example, entering “What is the individuals first given name?” is a good description for the first name field.
Data type and Size properties
The data type property determines the type of values that can be entered into the field. Many data types have a related size property that further limits the data that can be entered in the field. For example, a StreetAddress field would be a text field with a size of 50 characters and a State field would be a text field with a size of two characters. A Quantity field in an ordering table would be a number field, and its size would be either be an integer (number with no decimal places) or long integer (large number with no decimal places).
The table below lists the various data types and related size options available in an Access database.
[table id=”7″]
The table below lists the various numeric field sizes available in an Access database.
[table id=”8″]
Summary
In this post we have stated that a table field holds data just like a farmer’s field holds crops. We have seen that the table field is defined and controlled by its properties which are like the hedges and fences in the farmers field. We have also discussed that each field needs a name (designated by the name property), that the caption property provides a label for the field, that the description property can help a user know the purpose of the field, and that the data type and size properties can be used to limit the type and amount of data that can be planted in the field.
Quiz
See if you can answer the following questions. Click on the question to see the answer.[vc_toggle title=”If a table in Access is like a sheet in Excel, what is a field like?”]A field is like a column in a spreadsheet.[/vc_toggle][vc_toggle title=”What is a field property?”]A field property is an attribute that defines the size, data type, display or other setting of the field.[/vc_toggle][vc_toggle title=”What is the maximum number of characters you can have in a field name?”]The name can be up to 64 characters long.[/vc_toggle][vc_toggle title=”Can a field name include spaces?”]Yes it can have spaces, but spaces should be avoided because they can cause problems when used in calculations.[/vc_toggle][vc_toggle title=”Which property determines the default label that appears with a field on a form or report?”]The caption property determines the default label that appears with a field on a form or report. It also provide the column heading when the field is viewed in a table or query datasheet.[/vc_toggle][vc_toggle title=”What is the best data type to use for a zip code?”]Short text is the best data type for a zip code. Even though a 5 character zip code is composed of just digits, it should not be a number because those digits are not used in a mathematical calculation.[/vc_toggle][vc_toggle title=”A numeric field named Children tracks the number of children a parent has. What should its field size NOT be?”]The field size should NOT be a single, double, nor decimal because they allow decimal places. Nobody has 1.5 children. The byte field size will be more than sufficient because it has a maximum of 255 which would be a lot of children.[/vc_toggle]