Every table in Access is made up of fields. The properties of a field describe the characteristics and behavior of data added to that field. A field's data type is the most important property because it determines what kind of data the field can store. This article describes the data types and other field properties available in Access, and includes additional information in a detailed data type reference section. Show
In this article
OverviewData types can seem confusing for example, if a field's data type is Text, it can store data that consists of either text or numerical characters. But a field whose data type is Number can store only numerical data. So, you have to know what properties are used with each data type. A field's data type determines many other important field qualities, such as the following:
A field's data type can be predefined or you will select a data type depending on how you create the new field. For example, if you create a field from the Datasheet view and:
Top of Page When to use which data typeThink of a field's data type as a set of qualities that applies to all the values that are contained in the field. For example, values that are stored in a Text field can contain only letters, numbers, and a limited set of punctuation characters, and a Text field can only contain a maximum of 255 characters. Tip: Sometimes, the data in a field may appear to be one data type, but is actually another. For example, a field may seem to contain numeric values but may actually contain text values, such as room numbers. You can often use an expression to compare or convert values of different data types. The following tables show you the formats available for each data type and explain the effect of the formatting option. Basic Types
Note: Attachment and Calculated data types aren't available in .mdb file formats. Number
Date and Time
Yes/No
OLE Object OLE objects, such as Word documents. Top of Page Field size propertyAfter you create a field and set its data type, you can set additional field properties. The field's data type determines which other properties that you can set. For example, you can control the size of a Text field by setting its Field Size property. For Number and Currency fields, the Field Size property is especially important, because it determines the range of field values. For example, a one-bit Number field can store only integers ranging from 0 to 255. The Field Size property also determines how much disk space each Number field value requires. Depending on the field size, the number can use exactly 1, 2, 4, 8, 12, or 16 bytes. Note: Text and Memo fields have variable field value sizes. For these data types, Field Size sets the maximum space available for any one value. For more details about field properties and how they work with the different data types go to the Data type reference section. Also read the article Set the field size. Top of Page Data types in relationships and joinsA table relationship is an association between common fields in two tables. A relationship can be one-to-one, one-to-many, or many-to-many. A join is a SQL operation that combines data from two sources into one record in a query recordset based on values in a specified field that the sources have in common. A join can be an inner join, a left outer join, or a right outer join. When you create a table relationship or add a join to a query, the fields that you connect must have the same or compatible data types. For example, you cannot create a join between a Number field and a Text field, even if the values in those fields match. In a relationship or a join, fields that are set to the AutoNumber data type are compatible with fields that are set to the Number data type if the Field Size property of the latter is Long Integer. You cannot change the data type or the Field Size property of a field that is involved in a table relationship. You can temporarily delete the relationship to change the Field Size property. However, if you change the data type, you won't be able to re-create the relationship without first also changing the data type of the related field. For more information on tables see the article, Introduction to tables. Top of Page Data type referenceWhen you apply a data type to a field, it contains a set of properties that you can select. Click on data types below for more information.
AttachmentPurpose Used in a field that allows attaching files or images to a record. For example, if you have a job contacts database, you can use an attachment field to attach a photo of the contact, or attach documents such as a resume. For some file types, Access compresses each attachment as you add it. Attachment data types are available only in .accdb file format databases. Types of attachments that Access compresses When you attach any of the following file types, Access compresses the file.
You can attach many kinds of files to a record. However, some file types that may pose security risks are blocked. As a rule, you can attach any file that was created in one of the Microsoft Office programs. You can also attach log files (.log), text files (.text, .txt), and compressed .zip files. For a list of supported image file formats, see the table later in this section. List of blocked file types Access blocks the following kinds of attached files.
Supported field properties
Supported image file formatsAccess supports the following graphic file formats without the need for additional software being installed on your computer.
File naming conventionsThe names of your attached files can contain any Unicode character supported by the NTFS file system that is used in Microsoft Windows NT. In addition, file names must follow the following guidelines:
Top of Page AutoNumberPurpose Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key, especially when no suitable natural key (a key that is based on a data field) is available. An AutoNumber field value requires 4 or 16 bytes, depending on the value of its Field Size property. Suppose that you have a table that stores contacts' information. You can use contact names as the primary key for that table, but how do you handle two contacts with exactly the same name? Names are unsuitable natural keys, because they are often not unique. If you use an AutoNumber field, each record is guaranteed to have a unique identifier. Note: You should not use an AutoNumber field to keep a count of the records in a table. AutoNumber values are not reused, so deleted records can result in gaps in your count. Moreover, an accurate count of records can be easily obtained by using a Totals row in a datasheet. Supported field properties
Top of Page CalculatedPurpose Use to store the results of a calculation. The calculation must refer to other fields in the same table. You would use the Expression Builder to create the calculation. Note, Calculated data types were first introduced in Access 2010. Calculated data types are available only in .accdb file format databases. Supported field properties
Top of Page CurrencyPurpose Use to store monetary data. Data in a Currency field is not rounded off during calculations. A Currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right. Each Currency field value requires 8 bytes of storage. Supported field properties
Top of Page Date/Time and Date/Time ExtendedPurpose Use to store date and time-based data. Supported field properties
Top of Page HyperlinkPurpose Use to store a hyperlink, such as an e-mail address or a Web site URL. A hyperlink can be a UNC path or a URL. It can store up to 2048 characters. Supported field properties
Top of Page MemoPurpose Use to store a block of text that is more than 255 characters long and is formatted text. Note, beginning in Access 2013 Memo data type has been renamed to Long Text. Supported field properties
Top of Page NumberPurpose Use to store a numeric value that isn't a monetary value. If you might use the values in the field to perform a calculation, use the Number data type. Supported field properties
Top of Page Large NumberPurpose Use to store a Large numeric value that isn't a monetary value. If you might use the values in the field to perform a calculation, use the Large Number data type. Supported field properties
Top of Page OLE ObjectPurpose Use to attach an OLE Object, such as a Microsoft Office Excel spreadsheet, to a record. If you want to use OLE features, you must use the OLE Object data type. In most cases, you should use an Attachment field instead of an OLE Object field. OLE Object fields support fewer file types than Attachment fields support. In addition, OLE Object fields do not let you attach multiple files to a single record. Supported field properties
Top of Page TextPurpose Use to store up to 255 characters of text. Note, beginning in Access 2013 the Text data type has been renamed to Short Text. Supported field properties
Top of Page Yes/NoPurpose Use to store a Boolean value. Supported field properties
Top of Page What data type would be best suited to a field that lists customer email addresses?Names and email addresses are always of the type string, while numbers can be stored as a numerical type or as string since a string is a set of characters including digits.
Which data respiratory type is most likely to include database tables?Which data repository type is most likely to include database tables and primary keys in its internal data structure? A data warehouse, is a type of database, and most use tables, indexes, keys, and SQL queries.
Which of the following is a request for specific data from a database?A query can either be a request for data results from your database or for action on the data, or for both. A query can give you an answer to a simple question, perform calculations, combine data from different tables, add, change, or delete data from a database.
What programming language supports relational data?SQL or Structured Query Language is the primary interface used to communicate with Relational Databases.
|