Insert, change, or delete a Memo field

You use a Memo field when you need to store large amounts of text in a database. This topic explains how to use Microsoft Office Access 2007 to add a memo field to new and existing database tables.

What do you want to do?


Understand Memo fields

If you are new to Access, remember that the data in a database resides in one or more tables. You may view the information in a datasheet — a grid that resembles an Excel worksheet — or in a data-entry form or a report, but the data ultimately resides 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 Memo data type when you need to store large amounts of text.

Memo field specifications

Memo fields in Office Access 2007 can store up to 1 gigabyte of characters, or 2 gigabytes of storage (2 bytes per character), of which you can display 65,535 characters in a control on a form or report. In addition, you can now apply rich-text formatting to the data in a Memo field. For example, you can set colors, change fonts, and make data bold or italic.

For more information about how you can format rich-text data in a Memo field, see the article Format data in tables, forms, and reports.

Ways to create a Memo field

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

  • Datasheet view    You can add a Memo field to a new or existing table in Datasheet view by adding a new field and then either pasting more than 256 characters of text into a blank row in the field or selecting the Memo data type from a drop-down list.
  • Design view    You use Design view when you want to add a Memo field, enable rich-text editing for the field, and set properties that you can't set in Design view, such as a default value. For more information about the properties that you can set for a Memo field, see the section Memo field property reference.

You can use either technique when you create new tables and when you modify existing tables.

Top of Page Top of Page

Add a Memo field in Datasheet view

The steps in this section explain how to add a Memo field to a new or existing table in Datasheet view. If you're new to Access, a datasheet is a grid similar to an Excel worksheet.

Add a new Memo 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, like so:

A new field in a datasheet

  1. Double-click the header row and enter a name for the new field.

Typically, you apply names such as "Comments" or "Notes" to a Memo field.

  1. Select the first blank row under the header and enter a block of text longer than 256 characters.

Access applies the Memo data type to the field because the field contains more than 256 characters. Alternatively, you can paste more than 256 characters of text data into the first row.

  1. Save your changes.

Add a Memo 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.

  1. Double-click the header row for the first table field (the one labeled Add New Field), and type a name for the field. Typically, names such as "Comments" or "Notes" are applied to a Memo field.
  2. Select the first blank row under the header and type a block of text longer than 256 characters. Access applies the Memo data type to the field because the field contains more than 256 characters. Alternatively, you can paste more than 256 characters of text data into the field.
  3. Save your changes.

Top of Page Top of Page

Add a Memo field in Design view

You use Design view when you need to add a Memo field to a new or existing table, and then set the field to support rich-text formatting. The steps in this section explain how to add a Memo field to existing and new tables, and how to set the Memo field for rich-text editing.

Add a Memo 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 click Design View.
  3. In the Field Name column, select the first blank row and enter a name for the field. Typically, names such as "Comments" or "Notes" are applied to a Memo field.
  4. Select the adjacent cell in the Data Type column, and then select Memo from the list.
  5. Save your changes.

Add a Memo 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.
  3. Right-click the document tab for the new table and click Design View on the shortcut menu.
  4. In the Field Name column, select the first blank row and enter a name for the field. Typically, names such as "Comments" or "Notes" are applied to a Memo field.
  5. Select the adjacent cell in the Data Type column, and then select Memo from the list.
  6. Save your changes and if you want to enable rich-text editing for the field, continue to the next steps.

Enable rich-text editing

  • Under Field Properties, on the General tab, locate the Text Format property box and change the value from Plain Text to Rich Text.

 Note   If you bind a text box to your memo field, you must also set the Text Format property for the text box to Rich Text.

For information about formatting data as rich text, see the articles Format data in tables, forms, and reports and Enter or edit data in a control or column that supports rich text.

Top of Page Top of Page

Delete a Memo field

You can use Datasheet view or Design view to remove a Memo field from a table. However, when you delete a Memo 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 Memo 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 Memo field, right-click the header row (the name), and click Delete Column.
  2. Click Yes to confirm the deletion.

Delete a Memo 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 click Design View.

Access opens the table in Design view.

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

-or-

Right-click the row selector and click Delete Rows.

  1. Click Yes to confirm the deletion.

Top of Page Top of Page

Memo field property reference

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

Property Usage
Format

Enter custom formatting characters to define a display format. Formats defined here appears in datasheets, forms, and reports.

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

Caption Specifies the name of your Text field. 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, you could specify a value such as "Enter up to 65 KB of text." Doing so would remind users that they are working with a Memo field. Maximum length (of the default value): 255 characters.
Validation Rule

Specifies requirements for data entered into a record, field, or control. When you enter data that violates the rule defined for the field, 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 set to Yes, you must enter a value in the field, or in any controls bound to the field, and the value cannot be Null.
Allow Zero Length When set to Yes, you can enter zero-length strings in a field. A zero-length string contains no characters, and you use them 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 can use indexes 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 information. 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. Unicode takes up more storage space because it uses 2 bytes per character instead of one. To help reduce file size, the default value of the Unicode Compression property for a Text, Memo, or Hyperlink field is Yes. When set to Yes, any character whose first byte is 0 is compressed when it is stored and uncompressed when it is retrieved.

Data in a Memo field is not compressed unless it requires 4,096 bytes or less of storage space after compression. As a result, the contents of a Memo field might be compressed in one record, but might not be compressed in another record.

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

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 start 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 Format

Enables or disables rich-text editing. Select Rich Text to enable rich-text editing.

 Note   If you enable rich-text editing for the field, and then bind a text box control to the field, you must also enable rich-text editing for the control.

For more information on enabling rich-text editing for a control, and on using formatting in general, see the article Format data in tables, forms, and reports.

Text Align

Specifies the alignment for data in a Memo field. You have these choices:

  • General    Aligns text to the left, numbers and dates to the right
  • Left    Aligns all text, dates, and numbers to the left
  • Center    Centers all text, dates, and numbers
  • Right    Aligns all text, dates, and numbers to the right
  • Distribute    Justifies all text, dates, and numbers evenly against both sides of the field or text box
Append Only

Determines whether to track field value changes. There are two settings:

  • Yes   Tracks changes. To view the field value history, right-click the field, and then click Show column history.
  • No   Does not track changes.

 Warning   Setting this property to No deletes any existing field value history.

Top of Page Top of Page

 
 
Applies to:
Access 2007