Data types in Microsoft Access
Databases consist of tables, tables consist of fields and fields are of a certain data type. A field's data type determines what kind of data it can hold. Every database has data types for text, integers (integers are whole numbers, i.e. numbers without a fraction) and decimal numbers and so does Access. But Access also has some data types that are specific to Access, like the Hyperlink, Attachment and Calculated data type. In this article I will describe the Access data types and give an explanation of their use.
Choosing data types for your fields
When you create tables in Design View, Access lets you choose data types for your fields. you can access Design View by right clicking your table in the object list on the left of the screen and choosing Design View.
A field of type Number must contain numbers. If you try to put in text, Access won't like it and it will let you know.
Overview of Access data types
When you start using Access it is sometimes difficult to figure out what data type to use for a field. The table below gives you a general description of each data type in Access and situations where you would use the data type.
|Access data type||Used for|
|Text||Short (< 255 characters) like names, article titles, zip codes, short descriptions, etc|
|Memo||Long texts like article body text, blog posts, long descriptions|
|Number||Quantity, size, length, weight, speed, scores, percentages|
|Date/Time||Birth date, order date, expiration date, creation date, subscription date|
|AutoNumber||Primary key fields|
|Yes/No||Yes/No situations like 'wants to receive newsletter or doesn't want to receive newsletter', 'product is on offer or product is not on offer', etc.|
|OLE object||Used to embed or link to documents from other programs like Excel and Word.|
|Hyperlink||Used for hyperlinks|
|Attachment||Used to store files in an Access database. The attachment data type lets you store one or more files per record.|
|Calculated||Calculated fields allow you to store the result of a calculation that includes data from other fields. Storing data that can be derived from other fields should however be avoided in database design. The use of calculated field is not recommended.|
|Lookup wizard..||A lookup wizard field lets the user choose from a predefined set of options, like a "male" or "female" selection or a "country" selection.|
Choose your data types wisely, and at the right time
Choosing the right data type for your fields is not rocket science, but it's best to get it right the first time, because changing a data type later when your table contains data can be tricky. If you try to change a field's data type when the table contains data for that field, than Access has to convert that data to the new data type and that is not always possible.
If, for example, your table contains a Text field that contains only numbers, Access will have no problem converting that field to the Number data type. But Access won't be able to convert a Text field to a Number field when the field contains non-numeric data like "hello, how are you" or "123ABC" or "I'm really craving a peanut butter jelly sandwich, but I am to lazy to get of the couch". Because there is no way for this data to be converted to numbers.
So, choose the right data type at the moment you create your tables. It could save you some trouble later on.
A field's data type does not only determine the type of data you can put in. It also determines the properties you can set on the field as you will see in the next sections.
The Text data type is used very often for names, descriptions, titles, et cetera. Text fields can contain a maximum of 255 characters of text.
Below is a picture of the Field Properties panel for a Text field. The Field Properties can be found by viewing your table in Design View and selecting one of its fields. The Field Properties are at the bottom of the screen.
Properties for the Text field
The most important properties of the Text are below.
|Field Size||The maximum number of characters you can enter. The largest maximum you can set is 255.|
|Format||Rules for the display of this field in the datasheet|
|Input mask||In input mask is a pattern that all data entered in this field must adhere to|
|Caption||When a caption text is entered, it is displayed as a field label in forms. If nothing is filled out, the filed name is used for this purpose.|
|Default value||The default value is inserted by Access automatically when no other value is supplied for the field.|
|Validation rule||A rule that validates the input. Read more on validation rules in the validation rules tutorial.|
|Required||Indicated whether a value is required for this field. Access will show an error message and won't insert the new record if you try to insert a new record that has no value for a required field.|
|Allow Zero Length||Indicates whether you are allowed to insert a text that contains zero characters.|
|Indexed||Apply an index when you want to allow for fast searching on the field's contents or when you want to impose a unique constraint (Select "Yes, no duplicates") on the field.|
The Memo data type is meant to hold text data, just like the Text data type. In contrast to the Text data type, Memo doesn't have a 255 character size limit. The Memo data type can hold somewhere around 65000 characters if you enter them through the Access interface and 1 Gigabytes if you enter the text programatically.
In Access 2007 and 2010, fields of the Memo data type can be configured to contain Rich Text. Rich Text is text to which formatting like bold and italic can be applied. In the background Access converts this styling to HTML.
The properties of the Memo data type are very similar to the field properties of the Text data type, so I won't describe them again.
The Text Format property lets you choose between Plain Text and Rich Text.
The Number data type in Access is a data type for different types of numbers, like Integer, Long Integer, Byte, Decimal, et cetera. The exact number type is configured using the Field Size property.
When creating a new Number field the Field Size property defaults to Long Integer. So by default, the Number field can contain whole numbers.
The Field Size property of the Number data type lets you choose a number type for the field. Below are the characteristics of each type.
|Byte||A Byte field allows whole numbers from 0 to 255.||-|
|Integer||The Integer field stores whole numbers between -32.768 and 32.767.||-|
|Long Integer||The Long Integer field stores whole numbers between -2.147.483.648 and -2.147.483.647.||-|
|Decimal||The Decimal field is used for storing decimal values. It has the largest precision of all Number fields. The properties of the Decimal field will let you choose the total precision (max 28) and the number of decimal places.||28|
|Single||For numeric floating point values that range from -3.4 x 1038 to +3.4 x 1038. The Single data type has a precision of 7 and is best not used for calculations that require a high level of precision.||7|
|Double||The Double data type stores numbers between -1.797 x 10308 to +1.797 x 10308||12|
|Replication ID||A replication ID is a long alphanumeric string that is used in database replication.||-|
The most common Field Size values are probably Long Integer and Double.
The Date/Time data type is used to store date values, time values, or combined date and time values. In the background Access always stores the date and time you enter like a number. Thedate format you specify determines what part of the date and time is shown in the Access data sheet.
In the field properties of a Date/Time field you can pick a format for your date field.
Note that what appears in the Format select list is dependent on your computer's region and language settings. The default date format in Windows on my computer is set to Dutch. That is why the Long date format is "dinsdag 19 juni" (tuesday, june 19th). Access will automatically show the date formats that are common to the region and language your computer is set to.
If you are not happy with the default date formats that Access offers you can also type in your own custom date format. To learn more about creating your own date format, look here.
One other nice feature of the Date/Time data type is the Show Date Picker property. When enabled(by selecting "For dates") Access will show a date picker in the data sheet when a date cell is selected.
Date picker widget for easy date selection
The Currency data type also adapts to the region and language settings of the operating system. But you can override this behaviour.
The currency data type is especially suited for calculations that involve currencies because it prevents rounding errors with very small fractions that are common to floating point Number types like Double.
In the Field Properties panel of a Curency field you will find a Format dropdown box with a number of predefined formats. Just like with Date/Time values, the formats that show up here are dependent on your computer's region and language settings.
Note that you can also type a custom value into the Format field. If you live in the US, but want to display your prices in Polish Zloty's (the zloty is the currency of Poland) you could enter a custom currency format like this:
This would tell Access to display the zł symbol behind currency values and to use a thousand separator (the comma).
If you have worked with databases before you probably know what AutoNumber is for. AutoNumber is a data type that is used to let Access generate unique numeric values for you. The AutoNumber data type is used on primary key fields, because primary keys must be unique and it is good practice to leave the job of generating new keys to the database.
When you create a new table in Access , Access automatically inserts an AutoNumber primary key field called ID for you.
The Yes/No datatype is called a boolean data type in most databases. The Yes/No data type is pretty simple. It just lets you chose between Yes and No :). This data type is used for yes/no questions. Does this user want to receive our weekly newsletter? Yes or no? Have we received payment for this order? Yes or No?
Yes/No fields appear as a checkbox in the data sheet.
The Hyperlink datatype is a text field, but a bit fancier. It won't surprise you that this field is used to store links. In the data sheet, the stuff you type into a Hyperlink field is automatically prepended with http:// by Access and it is displayed as a clickable link.
The Attachment datatype is used to store one or more files in a database field. If you set a field to the Attachment data type a small paperclip will display in the Access data sheet.
Double clicking the paperclick will open up a window that allows you to add one or more files from your hard drive.
The calculated data type lets you store the result of a calculation that includes data from other fields, like the amount of product x the product price. In database design it is considered bad practice to store information that can be derived from other fields, because you would be storing the same information in two locations and you would be forced to manage that information in two places.