Solved

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

Posted on 2011-09-05
4
1,025 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

808 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