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
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
These can be used to store larger amounts
of text and/or numbers up to 65,535 characters.
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.
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].
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.
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
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
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.
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.
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.