Text / short text

This is the default datatype for your fields which will be assigned by the database whenever you add a new field to a table

This datatype will allow the entry of textual and numerical values, although it will treat that latter differently from numerical values entered into a ‘number’ datatype field

Fields with this datatype will generally allow a maximum of 255 characters to be entered

Memo / long text

Fields with this datatype are used for lengthy texts and combinations of text and numbers

Up to 65,000 characters can be entered (the precise number may change depending on the database software being used)

Data in these types of field cannot be sorted

Data in these types of field are difficult, although not impossible, to query.

Number

This datatype allows the entry of numerical data that can be used in arithmetical calculations

There are several variations of this datatype, which control aspects of the numbers that can be entered, such as the size of the numbers, the number of decimal places and so on:

Byte: Stores numbers from 0 to 255 (no decimals)

Decimal: stores positive and negative numbers down to 28 decimal places

Integer: Stores numbers from –32,768 to 32,767 (no decimals)

Long integer: (default) stores numbers from –2,147,483,648 to 2,147,483,647 (no decimals)

Single: stores very large positive and negative numbers to 7 decimal places

Double: stores very large positive and negative numbers to 15 decimal places

Date/time

This datatype enables the entry of dates covering the period 100 through to 9999

This datatype can be customized in order to control the format of dates that are entered

Warning: in Microsoft Access, the Office autocorrect feature may well change some of your dates if it is active (e.g. “02/12/04” will autocorrect to “02/12/2004” unless you enter the year in full)

This datatype will allow the sorting of records chronologically, which the same values entered into a text datatype field would not (the latter would sort the records alphabetically – alphabetically “01/01/2010” would come before “31/12/1245”)

Currency

This datatype allows the entry of numerical values data used in mathematical calculations involving data with one to four decimal places, with the inclusion of currency symbols

Autonumber

This datatype automatically generates a unique sequential or random value whenever a new record is added to a table.

AutoNumber fields cannot be updated, that is, you cannot enter data into them manually

Yes/no / Binary / Boolean

A field with this type of datatype will only contain one of two values (Yes/No, True/False, or On/Off)

Quite often database software will represent this type of field as a checkbox in the table

The underlying text value of a yes/no field will be -1 (for yes) or 0 (for no)

OLE

A field with this datatype is one in which another file is embedded, as a Microsoft Excel spreadsheet, a Microsoft Word document, an image, a sound or video file, an html link, or indeed any other type of file

Every field in every table will need to have one of these datatypes assigned, and the decision as to which type is chosen should be factored into the database design process. For most fields the datatype chosen will be either ‘text’ or ‘number’. Keep in mind how these two datatypes treat numerical data differently, particularly in terms of how they cause data to be sorted:

• 1,10,11,2,20,21,3,4,5,6,7,8,9 is how data will be sorted if the datatype is ‘text’ (that is, alphabetically)
• 1,2,3,4,5,6,7,8,9,10,11,20,21 is how data will be sorted if the datatype is ‘number’ (that is, numerically)