Access 2007 Table Field Data Types
Go to: Previous Article Next Article
Part of the process of creating tables in an Access database is ensuring that each field has the appropriate data type. If the field type does not reflect the type of data entered into the field, then whenever the database is sorted, and when searches and queries are performed, erroneous results will be produced. Thus, for example, if you have a field called "date_of_birth" and you define it as a text field, you will still be able to input data into it. However, when you sort on this field, you will get an alphabetical sort, whereby "11/4/1969" will be placed before "12/7/1989".
There are 9 different data types to choose from in Access 2007 and each time you create a field, you need to pause for a second and think about the data it will contain and the range that that data will fall into. The Access data types are Auto Number, Number, Currency, Text, Memo, Date/Time, Yes/No, Hyperlink, Attachment and OLE Object.
The Auto Number data type is a specialised long integer which is used for primary key fields which need to contain a unique numerical value. As the name suggests, you cannot directly enter a value into a field defined with this data type; Access automatically assigns a new value each time a record is created.
The portmanteau Number field type should be used for all fields which contain values which are definitely numeric in nature such as a person's salary. Having chosen this data type, you can then specify a Field Size value in the Field Properties section of the table design view. Thus, if you had a field called "width", you might choose integer which allows you to input values up to approximately 32,000.
When defining fields which will store monetary values, you should consider using the Currency data type rather than the more general Number type. Currency is designed to perform accurate calculations on money-related figures. It allows 15 figures to the left of the decimal, and 4 to the right of, the decimal point.
The Text and Memo fields are both used for fields which will contain alphanumeric characters, such as "description", "telephone" or "firstName". The Text data type will accept a maximum of 255 characters while the Memo type can accept up to approximately 65,000 characters.
As in Microsoft Excel, Access treats dates and times as specialized number fields. The Date/Time data type is designed to accurately hold dates and times. Sorting, calculations and queries performed on this type of field will give the correct chronological results.
The Yes/No field type allows you to store Boolean (True or False) values. Examples where this field might be useful include "internal", "approved", "inStock", "female", etc.
With the advent of the web, database fields that contain URLs are fairly common. The Hyperlink data type is used to create a Microsoft style hyperlink, as found in Word and Excel. You can right-click on this field and choose Edit Hyperlink from the context menu to display the standard Microsoft dialog for editing hyperlink properties.
Access 2007 has two data types which can be used for storing binary data such as images and other documents: Attachment and OLE Object. Attachment is the more efficient of the two. As the name suggests, it works in a similar way to the attachments you add to an email. Access embeds the attached file in the table so that it becomes part of your database, increasing the file size accordingly. The OLE Object data type can be used in much the same way but is less efficient and exists principally for backward compatibility.
Article Source: Articlelogy.com
- Credit Cards A big selection of Cards in all flavors: Bad Credit Cards, Secured Cards, Prepaid Cards, Credit Cards for Canada, Low Interest Cards, etc -
Word Count: 614
Reduce Your Debts Without Bankruptcy. See How Much You Can Save. Free Debt Analysis
