Problem exporting over 255 characters in a field

[aswift]

Hi all,

I'm new here and attempted to search on this topic, but didn't seem to find anything pertaining to my problem...

I'm attempting to export a table using TransferSpreadsheet to an Excel 8-9 file, but one of the fields in my records is over 255 characters long. Importing it into Access works fine, but after I export it to the Excel file, it truncates the field to 255 characters. Is there a way around this?

I'm using Access 2000 and Excel 2000.

 

[alastair69]

You may need to change the Access field name to memo and try.

Alastair

 

[aswift]

I found out what my problem was. I'm running the imported table through a query. When I go to view the records that the query returns, that field is truncated. Is there a way to set the max length of a field or change the data type to memo that is being ran through a query?

Thanks again...

 

[Rickster57]

ABBY-Normal

I BELIEVE this is normal behavior when exporting a memo field from a query. If you insist on doing this through a query you will probably be forced to break that field up into parts using the mid function.

 

[RuralGuy]

Actaully Rickster, I believe the following link probably applies.
http://www.accessforums.net/ftopic5593.html

 

[Rickster57]

Thanks RuralGuy. I looked back at an old database where I had a similar issue and yep, I was grouping the query.

 

[RuralGuy]

No problem. We're all wiser now.

 

[Andrea Nalon]

I suggest to use the OutputTo command instead of TransferSpreadsheet and, as an output file format, use acFormatHTML. Then, if you append .xls in your output file you can open it as a regular excel sheet and then save as an excel workbook. It works and memo field are not truncated at 255 chars.
I didn't find out any other workaround to that issue, and keep in mind that if your MS Access query use a group by with a memo field, MS Access will truncate it a 255 character, even if you use the UNION clause.
Only the UNION ALL clause will not truncate memo fields coming from two or more queries.
Hope this will help you.

 

[pr2-eugin]

I suggest to use the OutputTo command instead of TransferSpreadsheet and, as an output file format, use acFormatHTML. Then, if you append .xls in your output file you can open it as a regular excel sheet and then save as an excel workbook. It works and memo field are not truncated at 255 chars.
I didn't find out any other workaround to that issue, and keep in mind that if your MS Access query use a group by with a memo field, MS Access will truncate it a 255 character, even if you use the UNION clause.
Only the UNION ALL clause will not truncate memo fields coming from two or more queries.
Hope this will help you.

Hello Andrea Nalon, Welcome to AWF

I am sure it would be helpful if the answer came through 8 years ago ! Nonetheless, it sure will help others who will see this in future !