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.
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.
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
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:
|Quantity * Price||Works just fine|
|Quantity * Sales Price||Produces an error because of the space|
|Quantity * [Sales Price]||Works because the name, which includes a space, is surrounded by square brackets|
|Quantity * SalesPrice||Works because the space has been removed|
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.
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.
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.
|Data Type||Purpose||Related Data Size||Examples|
|Short Text||For text and/or numbers not used in numeric calculations.||Up to 255 characters||First Name
|Long Text||For sentences and paragraphs.||Up to 63,999 characters||Notes
|Number||For numeric data that may be used in a mathematical calculation.||See numeric field sizes below||SquareFeet
|Large Number||For huge numbers that are greater than 2 billion.||Not applicable||WorldPopulation|
|Date/Time||For a date on a calendar and/or time of day.||Dates between 1/1/100 and 12/31/9999||BirthDate
|Currency||For money.||Up to 15 digits with 4 decimal places||Salary|
|Auto Number||Used to automatically enter a sequential number. Usually used as a record identifier.||Long Integer||RecordID
|Yes/No||For Yes or No / True or False values. Often displayed as a check box.||NA||IsValid
|OLE Object||For pictures, sounds, spreadsheets, documents, and other objects that are embedded into the field.||NA||ExpenseReport
|Hyperlink||For links to a file, website, email address, etc.||NA||EmailAddress
|Attachment||For files you would like to attach to the record. Similar to attaching a file to an email.||NA||ExpenseReport
|Calculated||For simple mathematical or text calculations from other fields in the table.||NA||FullName
The table below lists the various numeric field sizes available in an Access database.
|Field Size||Minimum||Maximum||Decimal Places|
|Single||As small as you want, up to 7 significant digits||As large as you want, up to 7 significant digits||7|
|Double||As small as you want, up to 15 significant digits||As large as you want, up to 15 significant digits||15|
|Decimal||As small as you want, up to 28 significant digits||As large as you want, up to 28 significant digits||28|
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.
See if you can answer the following questions. Click on the question to see the answer.
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.
What is a field property?
A field property is an attribute that defines the size, data type, display or other setting of the field.
What is the maximum number of characters you can have in a field name?
The name can be up to 64 characters long.
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.
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.
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.
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.