Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1284
  • Last Modified:

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

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
Bevos
Asked:
Bevos
3 Solutions
 
Kelvin SparksCommented:
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
 
Rey Obrero (Capricorn1)Commented:
see this link for possible cause and remedy

http://allenbrowne.com/ser-63.html
0
 
Nick67Commented:
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
 
BevosAuthor 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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now