Truncation of Text on Export to .txt file

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

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?
 
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.
 
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.
 
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.
 
Back
Top