Union query truncating a memo field

[jpkeller55]

I have a union query that is running against three different tables. Several of the fields in the tables are memo fields. When the union query executes, it is truncating the memo fields to 255 characters. I have looked at Allen Browne's advice at: http://allenbrowne.com/ser-63.html but nothing there seems to help. My code is:

SELECT tblCounsel1.temp, tblCounsel1.Seq, tblCounsel1.QuestID, tblCounsel1.ItemCode, tblCounsel1.Question, tblCounsel1.Response, tblCounsel1.RespOrder, tblCounsel1.ParameterText, tblCounsel1.ParameterDrug, tblCounsel1.ParameterClient, tblCounsel1.SHOW, tblCounsel1.HIDE, tblCounsel1.DrugName, tblCounsel1.RxStatus
FROM tblCounsel1
UNION ALL SELECT tblCounsel2.temp, tblCounsel2.Seq, tblCounsel2.QuestID, tblCounsel2.ItemCode, "" as [DUMMY FIELD 1], tblCounsel2.Response, tblCounsel2.RespOrder, "" as [DUMMY FIELD 2], "" as [DUMMY FIELD 3], "" as [DUMMY FIELD 4], tblCounsel2.SHOW, tblCounsel2.HIDE, "" as [DUMMY FIELD 7], "" as [DUMMY FIELD 8]
FROM tblCounsel2
UNION ALL SELECT tblCounsel3.temp, tblCounsel3.Seq, tblCounsel3.QuestID, tblCounsel3.ItemCode, "" as [DUMMY FIELD A], "" as [DUMMY FIELD B], "" as [DUMMY FIELD C], tblCounsel3.ParameterText, tblCounsel3.ParameterDrug, tblCounsel3.ParameterClient, "" as [DUMMY FIELD D], "" as [DUMMY FIELD E], "" as [DUMMY FIELD F], "" as [DUMMY FIELD G]
FROM tblCounsel3
ORDER BY Seq;

Can anybody tell me why this is truncating the memo fields? Thanks!

[ajetrumpet]

jim,

what do you mean "nothing seems to help". are you saying that you've tried all of his workaround solutions??

[June7]

I have also experienced this with UNION query. I don't think there is a fix. UNION query can't handle string values longer than 255 characters. As Allen Browne explained, this is by design.

What is it you want to do with this UNION? Will you base a report on this data? Solution for my situation was to have a subreport for each table.

EDIT: Keep reading if you want, apparently the fix is to use UNION ALL.

[jpkeller55]

Hi guys, thanks for responding. I have reviewed what Allen Browne has in his table and have incorporated the UNION ALL syntax and I also tried using the workaround suggested for Concatenated fields. I know this can be fixed as I had this working when the union was based of a table and a query. See the test database at: https://www.accessforums.net/queries...ters-8459.html

Just not sure why I can't get this working on three tables with a union. Maybe something to do with the data types of all the fields not being memo? I just don't know.

[jpkeller55]

Here is the original test query I had from my previous post as an fyi.

[June7]

Tried UNION, UNION ALL with my data and still truncates, even when the only field retrieved is the memo field. The field name is the same in all 3 tables and so there is no alias involved. I know what you're thinking - why do I have 3 tables - I originally tried to set up with one table but aspects of this project made that not practical. Since I can view the full memo field in subforms and subreports and not necessary to be able to sort/filter the 3 tables together, UNION not really needed. I just thought it would be a quick way to build the report and that's when I learned about the truncating and determined would have to use subreports.

I ran the query from your test project and the field truncates. Did you say this was working for you?

[jpkeller55]

Try this file....this one was working.

[June7]

Yes, that one worked so made another attempt with my data. WOW, don't know what I did differently but UNION ALL on my three tables worked - no truncating and no intermediate query, directly with tables. It's a very simple query because all the fields are same and in same order in tables.
SELECT * FROM CommentsProjects
UNION ALL SELECT * FROM CommentsFinancial
UNION ALL SELECT * FROM CommentsContracts;

Lovin' it - learn something new! To make this work for my report would have to include JOINs between each of the Comments tables and respective parent tables. Maybe will play with but not critical.