Access export to Excel truncates string fields longer than 255 characters

Question

Hi, everybody!

As I found this issue on a post with last update in Sep'2020 I decided to start a new one. I apologize if this is not the correct procedure.

Related topic: https://social.msdn.microsoft.com/forums/Lync/en-US/5e7e6fe9-6aa9-4af2-a995-1de5e5f1a110/exporting-a-query-from-access-to-excel-causes-fields-to-be-truncated-to-255-characters?forum=accessdev

Environment: Access 2010 running under Windows 7 & 10, both 32 & 64 bits.

Problem, aka my only problem until I read that post:

• I have an application developed in Access .mdb type DB because security by user was an excluding requirement.
• It's updated daily in the morning and then exports data to an Excel workbook used to generate Word documents using mail merge functionality, which is dowloadable locally on each machine.
• Everything worked fine, even with Memo fields, until the exportation query include fields defined using functions which returned strings longer than 255.
• The query is exported by a VBA procedure using TransferSpreadsheet method:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qdf.Name, psFile, True

Problems, aka my new problem after reading it:

• As Dirk Goldgar @Dirk %20goldgar stated in a couple of answers that the issue was the @ format of the field, I got stumped at this: "I downloaded and looked at it, and it's as I originally suspected, and you denied: the Format property of the Description field is set to "@" in both the table and the query.".
• I can handle the @ at fields on table definition buy I don't get where can I do that on query fields, even I checked all of its properties.

Resuming:

• How can I solve the 255-truncation issue for non table fields but defined in the query (select a, b, c, sfunction(a,b,c) as [Non table field] from source...)?
• How can I change the format of query fields, both direct from tables -if needed- and defined in query?

Thanks in advance for your time.

Best regards.

Answer 1

I am not sure if this will answer what you are asking, but I will try.

Resuming:

1. How can I solve the 255-truncation issue for non table fields but defined in the query (select a, b, c, sfunction(a,b,c) as [Non table field] from source...)?
   For your "non-table field", I think you need to make it an actual field. I would use a work table (a local table). Run the query and save the results from the query into the work table. Then export the work table records to Excel. In that way, you would have your field. I'm sure there are other ways to do it, but this is a quick (possible) solution.
2. How can I change the format of query fields, both direct from tables -if needed- and defined in query?
   I think here you are referring to the other article and maybe the directions in that other article were not very clear. The other article talked about the @ symbol in the description field (of the table or query).
   You said:
   "I can handle the @ at fields on table definition buy I don't get where can I do that on query fields, even I checked all of its properties."
   So, I'm assuming you want more specific instructions on how to find that.
   • Open the query in Design view.
   • Open the Property Sheet (on the Query Design menu, one of the last toggle buttons in the ribbon / tool bar)
   • Among the list of fields down below in the query grid, click on the Memo field that is being considered (put the cursor on its name). When you do this, the Property Sheet changes to the properties for this specific Memo field. One of the properties is the description field (the first one). For me, it is always blank, but for some people, apparently there is an @ symbol.