Click here for great value web hosting.

ALL ABOUT OFFICE

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT




Search for
 

help


ACCESS

Data Types

When creating a database you will have to decide on the most appropriate data type to use for your fields.  Although you could store everything in text fields, this would not allow you to perform calculations on your data.

Text Fields

Text fields can be used to hold a string of text and/or numbers up to 255 characters.  If you want to store more text than this then you should use a memo field.  When you create a text field, the default size is 50 but you should decrease this value if your field is going to hold smaller strings as it is good practise to make fields only as large as you need to save storage space.

Memo Fields

These can be used to store larger amounts of text and/or numbers up to 65,535 characters.

Numeric Fields

If you need to define a numeric field, you must make sure you complete the section at the bottom of the window to show the type of number which will be stored.  The window below shows a number field being defined.

You can use the Field Size box to choose the type of number, most commonly used are Double for numbers with decimal places and Long Integer for whole numbers.  Once you have done this, you can either choose one of the pre-set formats for your numbers from the Format box or you can set your own by using the hash (#) character to represent figures, commas (,) to represent thousands separators and the decimal point to show how many decimal places you want displayed.  The example above shows the format #,###.## which will allow numbers up to 9,999.99 to be entered.  There is a separate Data Type for Currency fields.

Note - if you want to define a field to hold a telephone number , it is best to use Text format because using Numeric format would cause the first zero to be cut off as we do not start normal numbers with zero.  This also allows you to use punctuation and brackets and to write extension numbers in a telephone number field [eg (020) 8680-1234 Ext. 999].

Currency Fields

If you choose the Currency data type the field will automatically be set up using the local currency stored in the international settings on your computer.  If you have the wrong currency displayed or want to use a different currency, you can use the format field to enter the currency symbol and the hash signs, commas and decimal point to define your own format.  For example, you could define a euro field by typing €#,###.## as shown below.

Date/Time Fields

To define a date field, choose Date/Time from the Data Type list and then use the section at the bottom of the table design window to choose the type of date or time information which the field will hold as below.

If you do not want to use one of the preset date/time formats you can define your own by typing something like d/m/yy in the Format box.  This will format 8 April 2002 as 8/4/02.  If you wanted it to appear as 08/04/2002 you would use dd/mm/yyyy.  Similarly, hh:mm:ss and AM/PM can be used for formatting time.

Yes/No (Boolean) Fields

If you want a field to hold a Yes/No or True/False answer you can define it as a Yes/No field.  When you enter data in this field you can set up the format to be ‘True/False’, ‘Yes/No’ or ‘On/Off’ using the Format box at the bottom of the screen.  If you click on the Lookup tab you can use the Display Control box to set the field to a Check Box or Text Box.  You can click the Check Box to place a tick in it for a true value or enter the value ‘True’ or ‘Yes’ in the text box.

Autonumber Fields

If you want Acces to automatically give each of your records a unique reference number as they are entered you can create an Autonumber field.  You can choose for the value to increment with each record added (1, 2, 3, etc) or to be a random number.  Access will ensure that this value is unique for each record, this means that an Autonumber field is often a good index to use for a Primary Key.

OLE Object

If you choose this data type, you will be able to insert other objects into the field, such as Word documents, Excel spreadsheets, images or other files.  When you double-click on the data entered in the table it will open the linked object.

Hyperlink

If you choose the Hyperlink data type, the database user will be able to enter a URL into that field.  When the user clicks on the entered URL the corresponding web page or document is opened.

Click here to return to the Access index.

webmaster@allaboutoffice.co.uk