Truncation of Text on Export to .txt file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael - 23 Mar 2005 01:01 GMT

I have a query used to update our mySQL database online. One "created" field
in the query takes information in many different fields in the table and
creates a description. For example, if field A, B C and D stores the value
90, excellent, Null and 135. The description built would be "90% blue,
excellent stock, page 135" with field C (Null value) is ignored. There is
also a memo field in the table that is put into the description. This
description can become long, up to 600 characters. Most are 150 - 300
characters long.

The results of the query are then exported as a tab delimited text file.
When the description is over 370 - 380 characters, the description is
truncated. Same happens when I transfer the data into PageMaker via an ODBC
connection or if the created file is viewed in Excel.

Do I need to change a data type, use VBA code or make some other adjustment
to export the complete description? Or is there a limit to the number of
characters that can be exported in a field?

Signature

Thanks,
Michael

Reply to this Message

Ken Snell [MVP] - 23 Mar 2005 02:30 GMT

When you export the results of a query to a text file or an EXCEL
spreadsheet, all calculated fields within that query will truncate their
strings to 255 characters. See this Knowledge Base article:

Exported Query Expression Truncated at 255 Characters
http://support.microsoft.com/default.aspx?scid=kb;en-us;207668

Workaround would be to create a temporary table for the data (you could do
this via a maketable query), export the table, and then delete the temporary
table.

Signature

Ken Snell
<MS ACCESS MVP>

>I have a query used to update our mySQL database online. One "created"
>field
[quoted text clipped - 16 lines]
> to export the complete description? Or is there a limit to the number of
> characters that can be exported in a field?

Reply to this Message

Michael - 23 Mar 2005 21:08 GMT

Thanks. When I did a make table, I still had the query create a text field
with a 255 char limit.

What I did to work around that was change the data type of the description
field in the new table to memo. Then I changed the make table query and made
it an append query in the same table. I also created a delete query to
delete all information from the new table. Now I have a switchboard button
with VBA code that runs the delete query, then the append query. Whenever I
need to update, I just push a button. Now I am going to figure out how to
export the table to a tab delimited text file by VBA code and add it to the
same switchboard button I can create it all with one touch.

Thanks again for your help. It helped me get this figured out.

Signature

Thanks,
Michael

> When you export the results of a query to a text file or an EXCEL
> spreadsheet, all calculated fields within that query will truncate their
[quoted text clipped - 26 lines]
> > to export the complete description? Or is there a limit to the number of
> > characters that can be exported in a field?

Reply to this Message

Ken Snell [MVP] - 23 Mar 2005 23:09 GMT

Glad you figured it out. My suggestion regarding the "make table" query was
from memory... when I went back to read my previous notes, I found that you
do need to use an existing table and then append to that table.

Signature

Ken Snell
<MS ACCESS MVP>

> Thanks. When I did a make table, I still had the query create a text field
> with a 255 char limit.
[quoted text clipped - 49 lines]
>> > of
>> > characters that can be exported in a field?

Reply to this Message