MS-Access / Getting Started

---

Understanding Field Data Types

Access 2010 supports 11 types of data, each with a specific purpose. You can see the details about each data type in Table-1. Access also gives you a 12th option, Lookup Wizard, to help you define the characteristics of foreign key fields that link to other tables.

Table-1 Access Data Types

Data Type	Usage	Size
Text	Alphanumeric data	Up to about 1 gigabyte (GB), but controls to display a memo are limited to the first 64,000 characters
Memo	Numeric data	1, 2, 4, 8, or 16 bytes
Number	Dates and times	8 bytes
Data/Time	4 bytes (16 bytes for ReplicationID)	8 bytes
Currency	Monetary data, stored with 4 decimal places of precision	8 bytes
AutoNumber	Unique value generated by Access for each new record.	4 bytes (16 bytes for ReplicationID).
Yes/No	Boolean (true/false) data; Access stores the numeric value zero (0) for false, and -1 for true.	1 byte
OLE Object	Pictures, graphs, or other ActiveX objects from another Windowsbased application.	Up to about 2 GB
Hyperlink	A link "address" to a document or file on the Internet, on an intranet, on a local area network (LAN), or on your local computer	Up to 8,192 (each part of a Hyperlink data type can contain up to 2048 characters)
Attachment	You can attach files such as pictures, documents, spreadsheets, or charts; each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the size of a database file	Up to about 2 GB
Calculated	You can create an expression that uses data from one or more fields. You can designate different result data types from the expression.	Dependent on the data type of the Result Type property. Text data type result can have up to 243 characters. Memo, Number, Yes/No, and Date/Time should match their respective data types.
Lookup Wizard	The Lookup Wizard entry in the Data Type column in Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row	Dependent on the data type of the lookup field

For each field in your table, select the data type that is best suited to how you will use that field's data. For character data, you should normally select the Text data type. You can control the maximum length of a Text field by using a field property, as explained later. Use the Memo data type only for long strings of text that might exceed 255 characters or that might contain formatting characters such as tabs or line endings (carriage returns).

When you select the Number data type, you should think carefully about what you enter as the Field Size property because this property choice will affect precision as well as length. (For example, integer numbers do not have decimals.) The Date/Time data type is useful for calendar or clock data and has the added benefit of allowing calculations in seconds, minutes, hours, days, months, or years. For example, you can find out the difference in days between two Date/Time values.

Use the Date/Time data type to store any date, time, or date and time value. It's useful to know that Access 2010 stores the date as the integer portion of the Date/Time data type and the time as the fractional portion-the fraction of a day, measured from midnight, that the time represents, accurate to seconds. For example, 6:00:00 A.M. internally is 0.25. The day number is actually the number of days since December 30, 1899 (there will be a test on that later!) and can be a negative number for dates prior to that date. When two Date/Time fields contain only a date, you can subtract one from the other to find out how many days are between the two dates.

You should generally use the Currency data type for storing money values. Currency has the precision of integers, but with exactly four decimal places. When you need to store a precise fractional number that's not money, use the Number data type and choose Decimal for the Field Size property.

The AutoNumber data type is specifically designed for automatic generation of primary key values. Depending on the settings for the Field Size and New Values properties you choose for an AutoNumber field, you can have Access 2010 create a sequential or random long integer. You can include only one field using the AutoNumber data type in any table. If you define more than one AutoNumber field, Access displays an error message when you try to save the table.

Use the Yes/No data type to hold Boolean (true or false) values. This data type is particularly useful for flagging accounts paid or not paid, or orders filled or not filled.

The OLE Object data type allows you to store complex data, such as pictures, graphs, or sounds, which can be edited or displayed through a dynamic link to another Windowsbased application. For example, Access 2010 can store and allow you to edit a Microsoft Word document, a Microsoft Excel spreadsheet, a Microsoft PowerPoint presentation slide, a sound file (.wav), a video file (.avi), or pictures created using the Paint or Draw application.

The Hyperlink data type lets you store a simple or complex "link" to an external file or document. (Internally, Hyperlink is a memo data type with a special flag set to indicate that it is a link.) This link can contain a Uniform Resource Locator (URL) that points to a location on the World Wide Web or on a local intranet. It can also contain the Universal Naming Convention (UNC) name of a file on a server on your LAN or on your local computer drives. The link can point to a file that is in Hypertext Markup Language (HTML) or in a format that is supported by an ActiveX application on your computer.

The Attachment data type, introduced in Access 2007, is very similar to the OLE Object data type in that you can use it to store complex data. However, unlike the OLE Object data type, you can store multiple attachments in a single record. These files are stored in a binary field in a hidden system table. OLE objects usually result in database bloat because the files are not compressed, and Access also stores a bitmap thumbnail of the embedded file that can often be larger than the original file. For the Attachment data type, Access compresses each file, if it isn't already, and uses the original file rather than a generated thumbnail to minimize the amount of database bloat.

The Calculated data type, newly introduced in Access 2010, allows you to create a calculated result using an expression. The expression can include data from one or more fields. If you have a number field, for example, that holds quantity information for products purchased and a currency field that holds the price of a product, you can create a calculated field that multiplies the quantity and price fields and stores it with a result type of currency. You could also create a calculated field that concatenates first name, middle name, and last name fields and stores it with a result type of text for a field called Full Name. Access recalculates the value of the calculated field any time the dependent fields are changed.

RememberYou can use the Attachment and Calculated data types only with databases in the .accdb file type. If you plan to create a database in the older .mdb format and have users with previous versions of Access use this database, you cannot define any fields as Attachment or Calculated.