Union & Union All Queries, & Truncated Memo Fields

I am developing an Access 2007 database to manipulate products lists from a wholesaler into a CSV file to be imported into a Magento-based ecommerce website. This particular wholesaler provides three separate lists in Excel .xls format - one for each type of product, & one of the fields is a description field, which usually contains a great deal more than 255 characters, & is therefore a Memo rather than a Text field. Initially I just set up a UNION ALL query to join all the data together into one table that I could use as my product list, as UNION ALL rather than just UNION doesn't truncate the description Memo field.

Somewhat annoyingly, the description field contains not only some text which serves as a general description of each product, but also some other information which really should be in separate fields. And of course this information is different for the three types of product. I have therefore created a query for each of these tables (which are actually linked tables pointing at the downloaded Excel .xls files) which replace parts of the description fields with some HTML, so that the information is usefully displayed with headings etc. when it ends up on the website.

If my UNION ALL query works directly on the original linked tables, it works perfectly & doesn't truncate the Memo fields. However, if I try exactly the same query, but working on the queries which manipulate the original data, then the Memo fields get truncated. Why is this truncation happening?

I've tried various things to stop this happening - I've even tried having the UNION ALL query join these description-manipulating queries with the original tables, in the vague hope that it would not truncate the Memo fields, but for some reason it still truncates. So the only way I can have the UNION ALL query not truncating my Memo fields is by pointing to the original tables, but then I can't manipulate the information in those Memo fields as each of the three product types requires different manipulation of the description information.

I know there are other reasons why Access truncates Memo field data, but can't see why it's happening in this case. I can confirm that the three queries which manipulate the description information don't truncate the fields, so it is happening at the UNION ALL query stage, even though UNION ALL is not supposed to truncate.

Does anyone have any idea what's going on, or how I can fix it?

SELECT qryProdType1.[CAT NO], qryProdType1.TITLE, qryProdType1.DESCRIPTION, qryProdType1.CAT, qryProdType1.PRICE
FROM qryProdType1
WHERE qryProdType1.[CAT NO] IS NOT NULL
 
UNION ALL
 
SELECT qryProdType2.[CAT NO], qryProdType2.TITLE, qryProdType2.DESCRIPTION, qryProdType2.CAT, qryProdType2.PRICE
FROM qryProdType2
WHERE qryProdType2.[CAT NO] IS NOT NULL
 
UNION ALL 
 
SELECT qryProdType3.[CAT NO], qryProdType3.TITLE, qryProdType3.DESCRIPTION, qryProdType3.CAT, qryProdType3.PRICE
FROM qryProdType3
WHERE qryProdType3.[CAT NO] IS NOT NULL
 
ORDER BY CAT, [CAT NO];

Select allOpen in new window