CODEWITHSUNDEEP
sqlms-access
rohrl77
rohrl77

Reputation: 3337

Access Union Query has truncated memo field

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;```

Upvotes: 0

Views: 676

Answers (3)

rohrl77
rohrl77

Reputation: 3337

I opted to create a workaround for this particular situation. As the finaly query will be called by a script under any circumstances, I created a temp table that the 3 subqueries will be appended to. This way the memo field is not truncated. It's a second best solution for sure, but it works.

DoCmd.Hourglass True
CurrentDb.Execute "qry_del_temp_PublFinder_CB"
CurrentDb.Execute "qry_app_PublFinder_CB_1"
CurrentDb.Execute "qry_app_PublFinder_CB_2"
CurrentDb.Execute "qry_app_PublFinder_CB_3"
DoCmd.OpenQuery "qry_PublFinderUpload", acViewNormal
DoCmd.Hourglass False

Upvotes: 0

deluxeinformation
deluxeinformation

Reputation: 308

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

Upvotes: 1

iDevlop
iDevlop

Reputation: 25252

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.

Upvotes: 0

Related Questions