I have an Access UNION query, that is based on three subqueries, each of which has a memo field that contains long text.

In order for the three subqueries to work properly (i.e. displaying the full memo field text) I have already used the recommended workaround solution (First) by Allen Brown (http://allenbrowne.com/ser-63.html). However, these do not seem to work when I use UNION. The text gets truncated at 255 characters again.

How can I get one query to display the full text with all records from my three subqueries?

This is my current query:

FROM   _qry_publfinder_cb_other_advagreementno
UNION ALL 
SELECT *
FROM   _qry_publfinder_cb_other_advagreementyes
UNION ALL 
SELECT *
FROM _qry_PublFinder_CB_Rec;```

CodePudding user response：

That seems to be a documented 'feature':
https://answers.microsoft.com/en-us/msoffice/forum/all/union-query-truncates-the-field-contents-of-memo/f81ee760-ec69-4b5b-a709-6e533f8f66de

A UNION query removes all duplicate records. Since a Memo field could contain billions of bytes, the developers chose to avoid the possibly very time consuming task of comparing one billion byte memo field to another billion byte memo field by truncating all memos to 255 bytes.

If you don't need to deduplicate the records - i.e. if you're confident that the records in the different tables are already different - just use

UNION ALL

instead of UNION in the query. It won't try to deduplicate, it won't truncate, and as a bonus the query will run faster.

CodePudding user response：

If there is a single table common to all three subqueries that contains your memo field, try linking it in after you've done the union, e.g.

SELECT UQ.FLD1, UQ.FLD2, UQ.FLD3, tbl_with_memos.memo_field
FROM 
(SELECT FLD1, FLD2, FLD3 
FROM   _qry_publfinder_cb_other_advagreementno
UNION ALL 
SELECT SELECT FLD1, FLD2, FLD3
FROM   _qry_publfinder_cb_other_advagreementyes
UNION ALL 
SELECT SELECT FLD1, FLD2, FLD3
FROM _qry_PublFinder_CB_Rec) AS UQ
INNER JOIN tbl_with_memos
ON UQ.FLD1 = tbl_with_memos.FLD1