skip to Main Content
What Is A Field?

What is a field?

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

.period
!exclamation point
`accent grave
[open bracket
]close bracket

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:

Calculated expressionResults
Quantity * PriceWorks just fine
Quantity * Sales PriceProduces an error because of the space
Quantity * [Sales Price]Works because the name, which includes a space, is surrounded by square brackets
Quantity * SalesPriceWorks 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.

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.

Data TypePurposeRelated Data SizeExamples
Short TextFor text and/or numbers not used in numeric calculations.Up to 255 charactersFirst Name
Address
Phone
Zip Code
Long TextFor sentences and paragraphs.Up to 63,999 charactersNotes
Comments
NumberFor numeric data that may be used in a mathematical calculation.See numeric field sizes belowSquareFeet
Height
Width
Age
YearBuilt
Large NumberFor huge numbers that are greater than 2 billion.Not applicableWorldPopulation
Date/TimeFor a date on a calendar and/or time of day.Dates between 1/1/100 and 12/31/9999BirthDate
HireDate
StartTime
CurrencyFor money.Up to 15 digits with 4 decimal placesSalary
Auto NumberUsed to automatically enter a sequential number. Usually used as a record identifier.Long IntegerRecordID
OrderNumber
Yes/NoFor Yes or No / True or False values. Often displayed as a check box.NAIsValid
Archive
OLE ObjectFor pictures, sounds, spreadsheets, documents, and other objects that are embedded into the field.NAExpenseReport
Photo
AudioRecording
HyperlinkFor links to a file, website, email address, etc.NAEmailAddress
WebSite
FileLocation
AttachmentFor files you would like to attach to the record. Similar to attaching a file to an email.NAExpenseReport
Photo
Certificate
AudioRecording
CalculatedFor simple mathematical or text calculations from other fields in the table.NAFullName
ExtendedPrice

The table below lists the various numeric field sizes available in an Access database.

Field SizeMinimumMaximumDecimal Places
Byte02550
Integer-32,76832,7670
Long Integer-2,147,483,6482,147,483,6470
SingleAs small as you want, up to 7 significant digitsAs large as you want, up to 7 significant digits7
DoubleAs small as you want, up to 15 significant digitsAs large as you want, up to 15 significant digits15
DecimalAs small as you want, up to 28 significant digitsAs large as you want, up to 28 significant digits28

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.

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.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top