Which of the following data types is used to attach pictures, images, and office files?

Every field in a table has properties and these properties define the field's characteristics and behavior. The most important property for a field is its data type. A field's data type determines what kind of data it can store. MS Access supports different types of data, each with a specific purpose.

• The data type determines the kind of the values that users can store in any given field.
• Each field can store data consisting of only a single data type.

Here are some of the most common data types you will find used in a typical Microsoft Access database.

• If you use previous versions of Access, you will notice a difference for two of those data types.

• In Access 2013, we now have two data types — short text and long text. In previous versions of Access these data types were called text and memo.

• The text field is referred to as short text and your memo field is now called long text.

Here are some of the other more specialized data types, you can choose from in Access.

These are all the different data types that you can choose from when creating fields in a Microsoft Access table.

Data types are the building blocks of databases. A field’s data type not only influences other important characteristics of that field, such as field size, but also how the field is used throughout the database, such as in objects, calculations, expressions, and so forth. Using the right data type is a key to success.

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.

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.

Crystal Long teaches and develops with Microsoft Access, Excel, and Office and specializes in remote training. She connects and helps as your project is built. Visit www.MSAccessGurus.com for information. Share your perspective! Add your comments about the article below.

You may also like

Which of the following data types is used for storing text longer than 255 characters?

What is another name for a single unit of data?

Which of the following allows you to enter modify delete or view data records quizlet?

Which of the following should you do when you are finished working on a database file?