ACC: Concatenation of Memo Fields Creates Text Field

Last reviewed: May 28, 1997
Article ID: Q92892
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you use a make-table query to concatenate two Memo fields, the query concatenates the Memo fields into a Text field in the resulting table. If you have more than 255 characters combined in the two Memo fields, the data is truncated in the new Text field.

CAUSE

Text fields have a limit of 255 characters; Memo fields can contain up to 32,000 characters in Microsoft Access 1.x, and up to 64,000 characters in Microsoft Access 2.0 or later.

RESOLUTION

To work around this behavior, use an append query on an existing table with a Memo field and concatenate the two Memo fields into the existing Memo field. For example:

  1. Create a table with two Memo fields:

          Table: OldTable
          ------------------
          Field Name: Memo1
    
             Data Type: Memo
          Field Name: Memo2
             Data Type: Memo
    
    

  2. Create a table with one Memo field:

          Table: ExistingTable
          -------------------------
          Field Name: ExistingField
    
             Data Type: Memo
    
    

  3. Create the following new query based on the OldTable table:

          Query: TestMemo
          ------------------------------
          Type: Select Query
    

          Field: Concat: [Memo1]&[Memo2]
    
             Table: OldTable
    
    

  4. On the Query menu, click Append Query (or Append in Microsoft Access 7.0 or earlier). Type ExistingTable in the Table Name box.

  5. In the Append To row of the QBE grid, select ExistingField.

  6. Run the query.

MORE INFORMATION

Steps To Reproduce Behavior

  1. Perform steps 1 and 3 in the Resolution section of this article.

  2. Open the TestMemo query in Design view.

  3. On the query menu, click Make Table Query (or Make Table in Microsoft Access 7.0 or earlier). Type NewTable in the Table Name box.

  4. Run the query and note that the NewTable table contains a Text field called Concat with a field size of 255 characters.


Keywords : kbusage QryMktbl
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : kbworkaround


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.