Specifying the type of data

You learned in Chapter 2, “Create databases and simple tables,” that a field’s data type restricts entries in that field to a specific type of data. For example, if the data type is set

to Number and you try to enter text, Access refuses the entry and displays a warning. When setting the data type of a field in a table in Design view, you can choose from the

following types:

▪

Short Text Use for text fields that require up to 255 alphanumeric characters.

▪

Long Text Use for text fields that require up to 65,535 alphanumeric characters.

TIP When adding fields in Datasheet view, you can assign the Rich Text data type to fields that require up to 65,535 alphanumeric characters with character formatting. This is actually the Long Text data type with the Text Format property set to Rich Text instead of Plain Text.

▪

Number Use for numeric values. The size of the entry is controlled by the Field Size

property.

SEE ALSO For information about the possible settings for Number fields, see “Setting the field size” later in this chapter.

▪

Date/Time Use for dates in the years from 100 through 9999. Dates and times can

be expressed in a variety of formats.

▪

Currency Use for decimal values with up to 15 digits to the left of the decimal point

and up to 4 digits to the right.

▪

AutoNumber Use when you want Access to assign a unique number to each new

record. If you delete a record, its AutoNumber value is not reused, and remaining records are not updated.

▪

Yes/No Use for fields that can have only two possible mutually exclusive values, such

as True or False.

TIP In the database world, the Yes/No data type is more commonly called Boolean, in honor of George Boole, an early mathematician and logistician.

▪

OLE Object Use to hold a graphic or object such as a Microsoft Excel worksheet or

Microsoft Word document.

Specifying the type of data 159

6

▪

Attachment Use to attach a file to a record in the same way that you might attach a file to an email message.

TIP The Attachment data type can be assigned to a field only when the field is first created. You can’t assign the Attachment type to an existing field, nor can you change an Attachment field to another data type. For information about the Attachment data type, search for Attach files and graphics to the records in your database in Access Help.

▪

Calculated Use to hold the results of a calculation based on other fields in the same

table.

TIP The last option in the list displayed when you click the Data Type arrow in Design view is Lookup Wizard. For information about using this wizard, see “Allowing only values in lists” later in this chapter.

You can also click the More Fields button in the Add & Delete group on the Fields tool tab

to display a menu containing a list of data types with predefined properties that produce fields with common data type refinements.

In this exercise, you’ll use various methods to add fields of the most common data types to

a table. Then you’ll enter data to test the data type restrictions.

SET UP

You need the GardenCompany06 database located in the Chapter06 practice

file folder to complete this exercise. Be sure to use the practice database for this chapter rather than continuing on with the database from an earlier chapter. Open the database, and if you want, save your own version to avoid overwriting the original. Then follow the

steps .

1

On the Create tab, in the Tables group, click the Table button to start a new blank table with an ID field that has been assigned the AutoNumber data type.

Let’s add five fields with specific data types.

2

On the Fields tool tab, in the Add & Delete group, click the Short Text button. Then

change the selected field name to fShortText.

TIP If you use the name of a data type as the name of a field, Access warns you that the name might cause problems. We will identify all the fields in this table by their data type, preceded by the letter f.

3

Display the Click to Add list, click Currency, and then change the field name to

4

In the Add & Delete group, click the More Fields button to display a menu

containing a list of data types, some with refinements.

Clicking an option in the list sets the data type and any predefined property settings.

TIP At the bottom of the data types list is a group of Quick Start options that provide ready-made fields for common business tables. Clicking some Quick Start options, such as Address, inserts more than one field with the appropriate properties already set.

5

In the Number area of the list, click Standard. Then change the field name to

Specifying the type of data 161

6

In document Microsoft Access 2013 (Page 174-177)