Solved

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

Posted on 2011-09-05
4
1,006 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Bevos
4 Comments
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 166 total points
ID: 36486551
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
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 167 total points
ID: 36488179
see this link for possible cause and remedy

http://allenbrowne.com/ser-63.html
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 167 total points
ID: 36492768
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

0
 

Author Closing Comment

by:Bevos
ID: 36495494
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!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question