Concatenating in Query and Truncating Export

[electromarket]

Hi,



I am having a problem with a query that I am running and exporting.

Part of the query concatenates a couple of different parts of some tables that I have created and when I export the query to Excel it truncates this cell to 255 characters. If I export it with formatting and layout it truncates it to 4000 characters. When the query is run, its perfectly fine and you can view all the information to copy/paste into Excel. But as soon as you export it you have problems.

A bit more information.......

I am attempting to create product descriptions (all of which we have stored in out Access Database). To create these descriptions, here is what I have done;

Query 1 (creates all the product descriptions):
General Description - from a memo field in a Table
Features (html) - from a memo field in a Table
Specifications (html) - from a memo field in a Table
Video (html) - from a memo field in a Table
Images (html) - from a Query

Query 2 (creates the package descriptions by looking in a linked table which is exported from our Accounts system to find components and then concatenates the descriptions of the components):
Kit Description - from a Table
Component 1 Description - from Query 1
Component 2 (if necessary) Description - from Query 1
Component 3 (if necessary) Description - from Query 1
Component 4 (if necessary) Description - from Query 1
Component 5 (if necessary) Description - from Query 1

It is the Descriptions in Query 2 that are being truncated when exported to Excel.

Does anyone have any suggestions?

Dan.

[JoeM]

A common problem. See the following links for some suggestions:
http://social.msdn.microsoft.com/For...field-contents
http://allenbrowne.com/ser-63.html

[electromarket]

Everyone sends me to that Allen Browne site! I have read through it but I still cannot solve the problem!

[JoeM]

Did you look at that other link?
The person in the first reply documented many different methods, and claims certain ones will work.

[electromarket]

Yeah I had a look at the other link but that is for Access 2000, I am running on Access 2007.

[JoeM]

Yeah I had a look at the other link but that is for Access 2000, I am running on Access 2007.

Did you try the suggestions provided there?
Many times, what works on Access 2000 will work on Access 2007.

Of course, you also have an obvious workaround should none of those work. Export the fields individually and perform your concatenation in Excel.

[electromarket]

The suggestions in there are go to File > Export. Access 2007 does not have this.

I attempted to export individually and do the concatenations in Excel but the file was so big that it just kept on lagging and virtually crashing out on me.

[JoeM]

The suggestions in there are go to File > Export. Access 2007 does not have this.

I believe the equivalent of that in Access 2007 is to just right-click on the query name and select Export.

Alternatively, it can be found on the External Data menu, on the Export ribbon.