Figure 1 shows the Data Type dropdown menu for when you define fields in an Access table. Some of the data types are simple and straightforward while others are complex. It can be helpful to think of them as general categories of data, with the Field Size property providing more specifics about the “true” data type. For example, a field with the Data Type property set to Number could be a long integer, decimal, or some other type of number. Which of those it will be is determined by the Field Size property. Figure 2 shows data types used in our sample database.
Figure 1
Figure 2
TEXT
Short Text (formerly Text) is a simple data type. A field with this data type is limited to a maximum of 255 characters in length, which is controlled by field size. It’s a good idea to consider the actual length of the data that will be used in that field and set the field size accordingly. For example, if you have a code that’s only seven characters, limiting the field size to 7 results in better performance and helps reduce errors, such as someone entering an invalid eight-character code.
By contrast, Long Text (formerly Memo) is a complex data type because it’s stored in “pages”—and you wouldn’t want to use an entire piece of paper for writing just one line! A Long Text field can store up to 65,536 characters if the data is entered into Access directly. Because Long Text is stored differently, it can create issues, so use it sparingly, if at all—a better strategy is to store external files externally. Generally, it’s best to stick to simple data types.
NUMBER
For Number, the critical distinction is made in the Field Size property: Byte, Integer, Long Integer, Single, Double, Replication ID, and Decimal.
AutoNumber is a special form of Long Integer that’s generally used for primary keys because it automatically gets a value when a new record is created. By default, autonumber values start at 1 and increase in increments of 1.
DATE/TIME
Date/Time is stored as a number. The “whole” part of the number measures the days that have passed since December 30, 1899. The decimal portion indicates the fraction of a day, where midnight is 0 and noon is 0.5. Date/Time is a special data type since many pieces of information are reported in the one value. You can show just the date, just the time, or a combination of both—all in various display formats.
CURRENCY
Currency is a fixed-point number with four decimal places. It’s accurate for storing financial (and other) values that need precision but don’t exceed four decimal places. The upper limit for Currency is 922 trillion. By default, values will display using the Currency symbol specified in the Windows Region Settings.
YES/NO
Yes/No fields can only contain one of two values: Yes or No, True or False, On or Off. It can never be null. This can create issues, so it can be more like a complex data type that needs special handling. Sometimes, you may choose to use the Integer data type instead.
OLE OBJECT
OLE Object allows you to embed files created by other applications, such as Word or Excel. It’s usually better to keep these as separate files and store the path and filename in your database.
HYPERLINK
Hyperlink is a special form of text that will act as a link to open a file when you click on it. You can define the text to display, the actual address of the file, a named section within the file, and a screen tip. It’s usually better to use Short Text so you have more characters for the actual address, and then use VBA to open the file (e.g., FollowHyperlink [MyFieldWithPathFile]).
ATTACHMENT
This is for attaching images and other files in your database, like you would attach a file to an email. In most cases, it’s better to store the pathfilename and leave the files external to the database.
CALCULATED
This data type calculates values from an equation. Calculated fields can create issues, such as when sorting, so you may want to instead use data macros that will trigger calculations and store the actual results—or do calculations in queries.
LOOKUP WIZARD
The Lookup Wizard data type should only be used for web databases. Using the Lookup Wizard can cause problems in a desktop database.
Download this month’s database: sf-1702-DataTypes_2.
SF SAYS
Choosing the right data type goes hand-in-hand with properly defining which fields go in which tables.
February 2017
