union query, cuts off data of merged memo field

[Philip Leduc]

Hi ,

I am working on a database that was build by someone else where I need to
merge two tables (from different forms) with a simlar (data) structure in to
one
query for a report.
Using an union query, I do notice that I lose data of the memo field , it
seems like to cut of at a certain point?
How do I get the entire memo field in my merged query?

 

[Allen Browne]

Access truncates the memo field at 255 characters if your query performs any
aggregation on the field. Since a UNION query de-duplicates records, it will
truncate the fields at 255 characters.

A UNION ALL does not de-duplicate.
Try replacing UNION with UNION ALL.

 

[Guest]

hi allen, ive tied using union all select... with no luck, my memo fields get
truncated.
please advise

 

[Allen Browne]

Access truncates the memos if it has to use them in a comparison.

Examples of what might cause that:
- using UNION rather than UNION ALL;
- a DISTINCT predicate;
- a GROUP BY clause that includes the memo.

If your query has a GROUP BY clause, you can solve the issue by using First
instead of Group By under your memo field in the Total row.

Additionally, the field will truncate if there is anything in the Format
property of the text box on your form/report, or anything in the Format
property of the memo field in table design.