Memo fields truncated in Access query, how to fix and export to excel?

Bevos
Bevos used Ask the Experts™
on
Hello, I have a query which contains several memo fields.  I want to export the results of this query to Excel, but within Access 2007 the memo fields are truncated in the query.  How can I change this and make a clean export of the query and data?  In the Access query I use a few SQL commands: IN() HAVING() WHERE() and INNER JOIN() as well as merging some text fields.  Can anyone offer me any advice?

Thank you,
Bevo
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Yes, you must write the results of the query to a ble and export the table contents. Any query with men=mo fields being exported will truncate those filds to 255 characters. Turn your query into a "make table" (or using SQL select fields.... INTO tablename will create the table) and run this. If you turn earnings off before running it and ON after, you should not get the warning that the query is about to overwrite the bale.


Kelvin
Top Expert 2016
Commented:
see this link for possible cause and remedy

http://allenbrowne.com/ser-63.html
Most Valuable Expert 2014
Commented:
You are up against it in some ways my friend!
When you do a copy and paste to Excel, things get truncated to text size (max 255 characters)
You can do a full export using TransferSpreadSheet or CopyFromRecordset
But Excel is only going to show limited amounts of that memo data in a cell
http://www.dbforums.com/microsoft-excel/970380-how-many-characters-does-excel-cell-hold.html
Likely only 1024 characters--and carriage returns play into that too.
It all displays in the formula bar--but that's NFG for usefulness.

If you have big memo fields, Excel will not be a useful export medium.
Mail merge to Word, or VBA code to craft custom HTML files is about it
'create an html page in the correct location
Dim fs As Object
Dim wait As Double
Dim BatFile As TextStream
Dim myarray() As String
Dim AttachmentPath As String
Dim success As Boolean

Set fs = CreateObject("Scripting.FileSystemObject")
Set BatFile = fs.CreateTextFile("\\myserver\webpdf\" & myfilename & ".htm", True)


'write the html for the page

BatFile.WriteLine ("<html>")
BatFile.WriteLine ("<head>")
BatFile.WriteLine ("    <title>" & Nz(Me.SomeField, "") & " Reports</title>")

BatFile.WriteLine ("</head>")
BatFile.WriteLine vbCrLf
BatFile.WriteLine vbCrLf

BatFile.WriteLine ("<p>Hello Bevos</p>")

BatFile.WriteLine ("</body>")
BatFile.WriteLine ("</html>")

BatFile.Close
Set BatFile = Nothing

Open in new window

Author

Commented:
Thanks so much for all the advice! I tried out the URL to allen brown and it was very helpful.  The VBA feedback for mail merge is going to come in handy for future operations too!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial