Solved

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

Posted on 2011-09-05
4
1,048 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
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.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

751 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