Contents of the Memo field may appear as control characters when you use the GROUP BY clause on the Memo field in a query in Microsoft Access (889490)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
  • Microsoft Access 2000
This article applies only to a Microsoft Access database (.mdb).

Novice: Requires knowledge of the user interface on single-user computers.

SYMPTOMS

When you use the GROUP BY clause on a Memo field in a query in Microsoft Access, the contents of the Memo field may appear as control characters that are similar to the following: "[]".

WORKAROUND

To work around this problem, use one of the following methods.
  • In the Design view of the query, change GROUP BY to FIRST in the Total row of the Memo field column.
  • In the Design view of the query, create an expression in a blank column, and use the Left function on the Memo field. For example, type the following in the Field row in the first blank column of the query:

    MyMemo: Left(Notes, 10000).

    Note When you sort or group on a Text field or on a Memo field in Access, only the first 255 characters are used to sort on or to group on.

STATUS

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

MORE INFORMATION

You may use the GROUP BY clause in a memo field and only the first 255 characters are returned from the query. We recommend that you do not use the GROUP BY clause on Memo fields in select queries.

Steps to reproduce the behavior

  1. Start Microsoft Access, and then open the sample Northwind.mdb database.
  2. In the Objects pane, click Queries, click New, click Design View, and then click OK.
  3. In the Show Table dialog box, double-click the following table names:
    • Employees
    • Orders
    • Order Details
    • Customers
  4. Click Close.
  5. In the Employees table, double-click the following field names:
    • EmployeeID
    • Notes
  6. On the View menu, click Totals.
  7. On the Query menu, click Run.
Note If you add the Shippers table to the query or if you re-arrange the order in which the tables are added to the query, the Notes column will return as expected.
Modification Type:MajorLast Reviewed:1/6/2005
Keywords:kbQuery kbDatabase kbtshoot kbprb KB889490 kbAudDeveloper kbAudEndUser
©2004 Microsoft Corporation. All rights reserved.