Microsoft KB Archive/889490

= 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 =

Article ID: 889490

Article Last Modified on 3/29/2007

-

APPLIES TO


 * Microsoft Office Access 2007
 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition
 * Microsoft Access 2000 Standard Edition

-



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: &quot;[]&quot;.



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 &quot;Applies to&quot; 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.

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:
 * 4) * Employees
 * 5) * Orders
 * 6) * Order Details
 * 7) * Customers
 * 8) Click Close.
 * 9) In the Employees table, double-click the following field names:
 * 10) * EmployeeID
 * 11) * Notes
 * 12) On the View menu, click Totals.
 * 13) 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:
 * 4) * Employees
 * 5) * Orders
 * 6) * Order Details
 * 7) * Customers
 * 8) Click Close.
 * 9) In the Employees table, double-click the following field names:
 * 10) * ID
 * 11) * Notes
 * 12) On the Design tab, click Totals in the Show/Hide group.
 * 13) 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.

Additional query words: ACC2000 ACC2002 ACC2007

Keywords: kbquery kbdatabase kbtshoot kbprb KB889490

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.