Exporting truncating Memo Fields (1 Viewer)

I have a query that is displaying exactly the correct results. However, when I export this to a text/tab delimited file (or even and XLS file) it truncates the memo field to 256 characters in the export file. I am sure it has something to do with this memo field being defined in part by a custom function. Below is the query and the function. The field in question is the "Formatting(First(description_text)) AS prod_Description" column. Any thoughts? Note: I know that if I don't perform the "First" on this memo field, during the group by, the query would truncate this to 256 characters becuase it has to be in the Group By clause. But by using the First function, this field does not need to be included in the group by and there for the query does not truncate it (even though the exporting does).

SELECT ProductList.cin_id AS prod_ID, Formatting([desc]) AS prod_Name, "" AS prod_Flag, "" AS prod_OverrideName, "" AS prod_SortName, Formatting(First(description_text)) AS prod_Description, "" AS prod_Bullets, ProductList.mfr AS prod_Mfr, "" AS prod_itemSort, "" AS prod_ProdGroup, "" AS prod_SubprodSequence, "" AS prod_Layout, "" AS prod_BaseProductID, "" AS prod_ItemSubheadAttr, "" AS prod_Keywords, "" AS prod_URL, "" AS prod_Type
FROM ProductList
GROUP BY ProductList.cin_id, Formatting([desc]), ProductList.mfr
HAVING (((ProductList.cin_id)<>''));

****

Public Function Formatting(Text As String) As String
Dim outString As String
outSring = ""
If Len(Text) > 0 Then
outString = Replace(Text, "<b>", "{\b")
outString = Replace(outString, "</b>", "}")
outString = Replace(outString, "<i>", "{\i")
outString = Replace(outString, "</i>", "}")
outString = Replace(outString, "°", "°")
outString = Replace(outString, "™", "™")
outString = Replace(outString, "©", "©")
outString = Replace(outString, "'", "'")
outString = Replace(outString, "®", "®")
outString = Replace(outString, "_{", "{^/")
outString = Replace(outString, "}", "^")
End If
Formatting = outString


End Function

Actually I figured it out. In the export dialog under advanced, even though it looks like you can only inlude the name, if you click very carefully to expand column that don't appear, you can change the data typ to memo

Google is giving thousand solutions and you still find yourself against the wall :banghead:
I was trying to fix the export truncate issue as described in the post, but I have no success.

I'm not sure where the problem is, because not all memo field records are truncated. I have 1200 characters in same column, just few rows above.


I also tried following:
- exporting a query instead of the table
- looking for hidden chars
- using different delimiter to separate fields and text qualifier = {none}

latter is changing memo field size and respects it, but this file format messes up the data.

This may or may not be helpful but I too was having an issue exporting a memo filed to Excel. From the research I did online it sounds like it was a know issue with Access that was addressed in later version.

I am using an Embeded Macro "On Click", under the Event tab in properties, to Export with Formatting ...

I am on 2007 but the problem was that I choose to export to Exel 97-2003 format (which seemed logical to me for-some-reason )


When I changed the field type to export to Excel Workbook (.xlsx) my problem has been solved and now I can export the the full field data. I am happy but can't believe it was that simple.

Help - still not working for me even after looking at the suggested solutions. I'm using 2010 and like others, trying to export a memo field from a query into an Excel workbook (.xlsx) and it's truncating it the data. Any other ideas how to get it to output the entire contents of the field?

Does it only truncate upon export or does it happen when you just run the query?

It only truncates on the export. I can see the full memo field in the query.

Your question, however, walked me right into a somewhat odd solution:

I've tested a few things:
- Copy/paste (rather than export) from the query into Excel truncates the field - Copy/paste from the table into Excel truncates the field
- Exporting a specified record from the table and selecting 'export only the selected records' does NOT truncate the memo field.
- Similarly, selecting all records in the query output and choosing 'export only the selected rows' in the Export Wizard, does NOT truncate the field.

I have no idea why that would teat that memo field any differently but it worked.

Thanks, Nymandus...you talked me into a solution!

GMV