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

Posted on 2009-04-15
Last Modified: 2012-05-06

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?



Question by:alexealden
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hello alexealden,

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



    Author Comment

    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,

    LVL 44

    Accepted Solution

    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.
    LVL 30

    Assisted Solution

    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.

    Author Comment

    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!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now