Select the product you need help with

ACC: Exported Query Expression Truncated at 255 Characters

Article ID: 178743 - View products that this article applies to.

Retired KB Content Disclaimer

This article was previously published under Q178743

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

Expand all | Collapse all

SYMPTOMS

When you export a query that contains an expression that results in more than 255 characters, the expression is truncated in the exported file.

Back to the top | Give Feedback

Microsoft Access evaluates the expression as a Text field, and Text fields have a maximum length of 255 characters. When you export the query, Microsoft Access truncates anything after the 255th character.

Back to the top | Give Feedback

RESOLUTION

Use an append query to append the data to a table containing a Memo field, and then export the table. The following example creates a table to store the results of a query expression that exceeds 255 characters:

Start Microsoft Access and open the sample database Northwind.mdb.

Create the following table in Design view:

        Table: EmpNotes
        --------------------
        Field Name: EmpName
           Data Type: Text
        Field Name: Comments
           Data Type: Memo

Save the EmpNotes table and close it. You do not need to create a primary key.

Create the following query in Design view based on the Employees table. The second expression, called Comments, will result in field lengths greater than 255 characters:

        Query: MemoExpr
        ------------------------------------------------
        Type: Select Query

        Field: Full Name: [FirstName] & " " & [LastName]
           Table: Employees
        Field: Comments: [HireDate] & ": " & [Notes]
           Table: Employees

On the Query menu, click Append Query.
In the Append dialog box, type EmpNotes in the Table Name box, and then click OK.
Select EmpName in the Append To row of the Full Name field in the QBE grid; the Append To row for the Comments field fills in automatically because the field name in the query and the field name in the table are the same.
On the Query menu, click Run. Click Yes when prompted that you are about to append the rows.
Close the MemoExpr query. You do not need to save it.
Export the EmpNotes table to whatever format you want. The full length of the Comments field will be preserved.

Back to the top | Give Feedback

STATUS

This behavior is by design.

Back to the top | Give Feedback

MORE INFORMATION

Steps to Reproduce Behavior

Start Microsoft Access and open the sample database Northwind.mdb.

Create the following query in Design view based on the Employees table:

      Query: qryTruncate
      -----------------------------------------------
      Type: Select Query

      Field: Full Name: [FirstName] & " " & [LastName]
         Table: Employees
      Field: Comment Length: Len([Comments])
         Table: Employees
      Field: Comments: [HireDate] & ": " & [Notes]
         Table: Employees

Save the qryTruncate query and close it.
Select the qryTruncate query in the Database window.
On the File menu, click Save As/Export.
In the Save As dialog box, click "To an External File or Database," and then click OK.
In the Save Query 'qryTruncate' In dialog box, select Text Files in the Save As Type box. Note the folder where the file will be stored in the Save In box, and then click Export.
When the Export Text Wizard appears, click Finish.
Using Notepad or another text editor, open the qryTruncate.txt file. Note that for any record where the Comment Length field is greater than 255, the Comments field is truncated at the end. This is true for the Andrew Fuller, Steven Buchanan, Michael Suyama, and Robert King records.

Back to the top | Give Feedback

REFERENCES

For more information about exporting data, search the Help Index for "exporting data," or ask the Microsoft Access 97 Office Assistant.

Back to the top | Give Feedback

Properties

Article ID: 178743 - Last Review: January 22, 2007 - Revision: 2.1

APPLIES TO

Microsoft Access 2.0 Standard Edition
Microsoft Access 95 Standard Edition
Microsoft Access 97 Standard Edition

kbfix kbhowto kbprb KB178743

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top | Give Feedback