Insert, create, or delete a field that stores text data

You add a Text field to a table when you need to store smaller amounts of textual data, such as names, addresses, and telephone numbers. This article explains how to use Microsoft Office Access 2007 to add and delete a Text field in new and existing database tables.

In this article


Understand Text fields

If you are new to Office Access 2007, remember that the data in a database is stored in one or more tables. You can view the information in a datasheet — a grid that resembles a Microsoft Office Excel 2007 worksheet — or in a data-entry form or a report, but the database stores all data in one or more tables. In turn, each table consists of a set of fields (columns), and each field is set to accept one specific type of data. For example, you set a field to the Date/Time data type when you need to store dates and times, and you set a field to the Text data type when you need to store textual data such as names, addresses, or telephone numbers. If you need to store large amounts of text (more than 256 characters), you set the field to the Memo data type.

Text field specifications

Text fields in Office Access 2007 can store up to 256 alphanumeric characters. You can display all 256 characters in the table field and in a control on a form or report. Unlike Memo fields, you cannot apply Rich-Text formatting to the data in a Text field. However, you can apply custom display formats, and you can also apply input masks that control how users enter data.

For more information about formatting Text data, see the article Format data in tables, forms, and reports. For information about input masks, see the article Create an input mask to enter field or control values in a specific format.

Methods for creating a Text field

Office Access 2007 provides several ways to add a Text field to a new or existing table:

  • Datasheet view    You can add a Text field to a new or existing table in Datasheet view by adding a new field and then typing text into a blank row in the field, or by pasting as many as 256 characters of text into a blank row. You can also select the data type from a drop-down list, and set properties such as Is Required, which forces users to enter a date in the field, and Is Unique, which forces users to enter a non-duplicate value in the field.
  • Design view    You use Design view to add a Text field and to set properties for the field that you cannot set in Datasheet view. Those properties include input masks and a default value for the field. For more information about the field properties that you can set in Design view, see Text field property reference at the end of this article.

Top of Page Top of Page

Add a Text field in Datasheet view

The steps in this section explain how to add a Text field to an existing table and a new table in Datasheet view. If you are new to Access, a datasheet is a grid similar in appearance to an Office Excel 2007 worksheet.

Add a Text field to an existing table

  1. Click the Microsoft Office Button Button image, and then click Open.
  1. In the Open dialog box, select and open the database.
  2. In the Navigation Pane, double-click the table that you want to change.

Access opens the table in Datasheet view.

  1. If necessary, scroll horizontally to the first blank field. By default, Access displays Add New Field in the header row of all new fields.
  2. Double-click the header row, and then type a name for the new field.
  3. Select the first blank row under the header, and then type a block of text or a combination of text and numbers. You can enter a maximum of 256 characters. Access infers the Text data type for the field when you enter text or a mix of text and numbers, and you enter no more than 256 characters. If you enter more than 256 characters, Access infers the Memo data type.

-or-

Paste up to 256 characters of text data into the first row.

  1. Save your changes.

Add a Text field to a new table

  1. Click the Microsoft Office Button Button image, and then click Open.
  1. In the Open dialog box, select and open the database.
  2. On the Create tab, in the Tables group, click Table.

Access opens the new table in Datasheet view. This figure illustrates a new table:

A new, blank table in a new database

  1. Click Save Button Image, and in the Save As dialog box, enter a name for the new table.
  2. Double-click the header row for the first table field (the one labeled Add New Field), and then type a name for the field.
  3. Select the first blank row under the header, and then type a block of text or a combination of text and numbers. You can enter a maximum of 256 characters. Access infers the Text data type for the field when you enter text or a mix of text and numbers, and you enter no more than 256 characters. If you enter more than 256 characters, Access infers the Memo data type.

-or-

Paste up to 256 characters of text data into the first row.

-or-

On the Datasheet tab, in the Data Type & Formatting group, from the Data Type list, select Text.

Top of Page Top of Page

Add a Text field in Design view

You use Design view to add a Text field to a new or existing table and then to set or change field properties that you cannot set or change in Datasheet view. For example, you can specify an input mask or a default value. The steps in this section explain how to add a Text field and set properties for the field.

Add a Text field to an existing table

  1. Click the Microsoft Office Button Button image, and then click Open.
  1. In the Open dialog box, select and open the database.
  2. In the Navigation Pane, right-click the table that you want to change, and then click Design View.
  3. In the Field Name column, select the first blank row, and then type a name for the field.
  4. Select the adjacent cell in the Data Type column, and then select Text from the list.
  5. Save your changes.

Add a Text field to a new table

  1. Click the Microsoft Office Button Button image, and then click Open.
  1. In the Open dialog box, select and open the database.
  2. On the Create tab, in the Tables group, click Table.
  1. Click Save Button Image, and in the Save As dialog box, enter a name for the new table.
  2. Right-click the document tab for the new table and click Design View.
  3. In the Field Name column, select the first blank row, and then type a name for the field.
  4. Select the adjacent cell in the Data Type column, and then select Text from the list.
  5. Save your changes. If you want to set properties for the field, leave the table open in Design view and go to the next steps.

Set or change field properties

  1. On the General tab in the lower section of the table designer, under Field Properties, locate the property that you want to change.
  2. Select the field next to the property name. Depending on the property, you can enter data (such as default text or an input mask), start the Expression Builder by clicking Button image, or select an option from a list.

For information about how to use each field property, select the property and then press F1.

Top of Page Top of Page

Delete a Text field

You can use Datasheet view or Design view to delete a Text field from a table. However, when you delete a Text field that contains data, you lose that data permanently — you cannot undo the deletion. For that reason, you should back up your database before you delete any table fields or other database components.

Delete a Text field in Datasheet view

  1. Click the Microsoft Office Button Button image, and then click Open.
  1. In the Open dialog box, select and open the database.
  2. In the Navigation Pane, double-click the table that you want to change.

Access opens the table in Datasheet view.

  1. Locate the Text field, right-click the header row (the name), and then click Delete Column.

Delete a Text field in Design view

  1. Click the Microsoft Office Button Button image, and then click Open.
  1. In the Open dialog box, select and open the database.
  2. In the Navigation Pane, right-click the table that you want to change and then click Design View.

Access opens the table in Design view.

  1. Click the row selector (the blank square) next to the Text field, and then press DELETE.

-or-

Right-click the row selector and then click Delete Rows.

  1. Click Yes to confirm the deletion.

Top of Page Top of Page

Text field property reference

When you use Design view to add a Text field to a table, you can set and change a number of properties for the field. This table lists the Text field properties, describes what each one does, and explains the implications of setting or changing them.

Property Usage
Field Size Controls the size of your Text fields. Valid values: 0 to 255. If you leave this property blank, the field accepts 256 characters.
Format

You enter custom formatting characters to define a display format. Formats defined here appear in datasheets, forms, and reports.

For more information about custom formats, see the article Format data in tables, forms, and reports.

Input Mask

You define an input mask when you need to control how users enter data in the field.

For more information about using input masks, see the article Create an input mask to enter field or control values in a specific format.

Caption Specifies the name of your Text field. This property accepts up to 2,048 characters. If you don't specify a caption, Access applies the default field name.
Default Value Specifies the value that automatically appears in a field when you create a new record. For example, in an addresses table, you can set the default value for the City field to a certain city. When users add a record to the table, they can either accept this value or enter the name of a different city. Maximum length: 255 characters.
Validation Rule

Specifies requirements for data entered into an entire record, an individual field, or a control. When a user enters data that violates the rule, you can use the Validation Text property to specify the resulting error message. Maximum length: 2,048 characters.

For more information about creating validation rules, see the article Create a validation rule to validate data in a field.

Validation Text

Specifies the text in the error message that appears when users violate a validation rule. Maximum length: 255 characters.

For more information about creating validation rules, see the article Create a validation rule to validate data in a field.

Required When this property is set to Yes, you must enter a value in the field or in any controls that are bound to the field. In addition, the value cannot be null.
Allow Zero Length When this property is set to Yes, you can enter zero-length strings in a field. A zero-length string contains no characters. You use it to indicate that you know no value exists for a field. You enter a zero-length string by typing two double quotation marks with no space between them ("") .
Indexed

You use an index to speed up queries, sorting, and grouping operations run against large amounts of data. You can also use indexes to prevent users from entering duplicate values. Choices:

  • No    Turns off indexing (default).
  • Yes (Duplicates OK)    Indexes the field and allows duplicate values. For example, you may have duplicate first and last names.
  • Yes (No Duplicates)    Indexes the field and does not allow duplicate values.
Unicode Compression

Access uses Unicode to represent data in Text, Memo, and Hyperlink fields. Because Unicode uses 2 bytes per character instead of 1, it takes up more storage space.

To offset this effect and help ensure optimal performance, Access sets the default value of this property to Yes for Text, Memo, and Hyperlink fields. When the property is set to Yes, any character whose first byte is 0 is compressed when it is stored and uncompressed when it is retrieved.

IME Mode Specifies an Input Method Editor, a tool for using English versions of Access with files created in Japanese or Korean versions of Access. Default value: No Control. For more information about using this property, press F1.
IME Sentence Mode Specifies the type of data you can enter by using an Input Method Editor. For more information about using this property, press F1.
Smart Tags

You specify one or more smart tags for the field and any controls bound to the field. Smart tags are components that recognize the types of data in a field and allow you to take action based on that type. For example, in an E-mail Address field, a smart tag can create a new mail message or add the address to a list of contacts.

Click Button image to see a list of available smart tags.

Text Align

Specifies the alignment for data in a Text field. Choices:

  • General    Aligns all text to the left.
  • Left    Aligns all text to the left.
  • Center    Centers all text.
  • Right    Aligns all text to the right.
  • Distribute    Justifies all text evenly against both sides of the field or text box.

Top of Page Top of Page

 
 
Applies to:
Access 2007