• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

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

Hi,

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?

Regards,

Alex

0
alexealden
Asked:
alexealden
2 Solutions
 
Patrick MatthewsCommented:
Hello alexealden,

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

Regards,

Patrick
0
 
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,

Alex
0
 
GRayLCommented:
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.
0
 
hnasrCommented:
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.
0
 
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!

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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