You receive a "The field is too small to accept the amount of data you attempted to add" error message when you run a query in Office Access (896950)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
  • Microsoft Access 2000

SYMPTOMS

In Microsoft Office Access, you may create a query that has the UniqueValues query property set to Yes, and the query may include a Memo field. When you run the query, you may receive the following error message:
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
Additionally, the query may run without an error message. However, the data that is returned in the memo field is truncated to 255 characters.

CAUSE

This problem occurs because when you set the UniqueValues query property to Yes, a DISTINCT keyword is added to the resulting SQL statement. The DISTINCT keyword directs Access to perform a comparison between records. When Access performs a comparison between two Memo fields, Access treats the fields as Text fields that have a 255-character limit. Sometimes Memo field data that is larger than 255 characters will generate the error message that is mentioned in the "Symptoms" section. Sometimes only 255 characters are returned from the Memo field.

WORKAROUND

To work around this problem, modify the original query by removing the Memo field. Then, create a second query that is based on both the table and the original query. This new query uses all the fields from the original query, and this new query uses the Memo field from the table. When you run the second query, the first query runs. Then, this data is used to run the second query. This behavior returns the Memo field data based on the returned data of the first query. To do this, follow these steps:
  1. Copy the original query, and then name this copy Backup Copy OriginalName.
  2. Click the original query, and then click Design on the Database toolbar.
  3. Click the column that contains the Memo field, and then click Delete on the Edit menu.
  4. Save the query.
  5. Double-click Create query in design view.
  6. Click the Both tab.
  7. Click the original query, and then click Add.
  8. Click the table that the original query is based on, and then click Add.
  9. Click Close.
  10. Add the fields from the original query, and then add the memo field from the table.
  11. Save the query.
  12. On the Query menu, click Run.

    The query should run as expected, and the Memo field is not truncated.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

For more information about problems that may occur when you use the UniqueValues query property, click the following article number to view the article in the Microsoft Knowledge Base:

304431 You receive a "Cannot include Memo, OLE or Hyperlink Object when you select unique values" error message

Modification Type:MinorLast Reviewed:4/19/2005
Keywords:kbdesign kbDatabase kbQuery kbtshoot kbprb KB896950 kbAudDeveloper kbAudEndUser
©2004 Microsoft Corporation. All rights reserved.