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


BUG #: 29350 (Content Maintenance) 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

Access 2000, Access 2002, or Access 2003

  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.

Access 2007

  1. Start Microsoft Access, and then open the sample Northwind.accdb database.
  2. On the Create tab, click Query Design in the Others group.
  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:
    • ID
    • Notes
  6. On the Design tab, click Totals in the Show/Hide group.
  7. On the Design tab, click Run in the Results group.
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.