Access / Excel 2007 I have a problem with data being truncated when pasted / exprted out of Access


Truncation in Access / Excel seems to cause me an awful lot of trouble ;( After much challenges to get a union query together that did not truncate my data in a particular field (over 255 charachters long)  I am now presented with another unexpected problem.

When I copy the query results from the Access 2007 and paste them into excel the description column is truncated down to 255 charachters even though it is absolutley fine in the database. I have found a quirky way round this iwhich is to paste special as text which solves it however ..

I would have expected to be able to export the data to a .csv, excel2007, or text file without truncation but no such luck - access faithfully truncates anything over 255 charachters .. arggh.  Ayone have any ideas to make this a little easier?



Who is Participating?
GRayLConnect With a Mentor Commented:
I believe that any time a memo field is used in a criteria or an ORDER BY, Jet will truncate the field to 255 characters.  If you can export the field without those contstraints, it should copy over intact.  Then do the operations in Excel.
Patrick MatthewsCommented:
Hello alexealden,

Have you tried opening an ADO or DAO recordset, and using Excel's CopyFromRecordset method?


alexealdenAuthor Commented:
Hi Patrick,

Thanks once again for your help! No I have not looked at this let me look into this and come back - Could you give me a pointer as to where I would find this function?

Kind Regards,

hnasrConnect With a Mentor Commented:
Usually we have key words for comparison, excluding moisy words such as a, an, and, the.
Memo field is to keep large amount of data, and not inteded to be used as criteria.

You may create your union query and use the limits of the memo field for comparison, then link this query with the original table to display the full memo values.
alexealdenAuthor Commented:
Hi !

Sorry for slow response I have been away recencly without access to web! Hnasr your solution sounds interesting but I am not sure exactly what you mean.

Are you saying that as long as I am displaying the contents of the memo field in the query on its own it should be ok, without truncation?

I have never created a relationship between a table and a query before normally just table - to table.

I will give this a go!

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.

All Courses

From novice to tech pro — start learning today.