Union query only returning 255 characters

[jpkeller55]

This is actually a follow up problem from my previous post at: https://www.accessforums.net/queries...rows-7825.html



I am running the attached Union query. The Question field is a memo field and this query is only returning the first 255 characters of that field. Does anybody know what I have to do to this code to get all the data from thie field to return?

select 1 as temp,first([Order]) as Sequence, Question, ""as Response,"" as RespOrder,"" as tblQuestResponseSets_SHOW, "" as tblQuestResponseSets_HIDE from tblTempCounsel group by Question
UNION select 2 as temp,[Order],"",Response, resporder, tblQuestResponseSets_SHOW, tblQuestResponseSets_HIDE from tblTempCounsel
ORDER BY [Sequence], temp, RespOrder;

Thanks much,

[weekend00]

replace UNION with UNION ALL.

[jpkeller55]

I am still getting the same results with UNION ALL. Any other suggestions?

[ajetrumpet]

Jim,

Is 'response' a memo field as well? Did you know that if you combine a text field with a memo in a union, it truncates because it automatically converts the combination to a text field type?

check out this link: http://allenbrowne.com/ser-63.html

Allen Browne is great. It reiterates some of the things already said, but there is more good info there to chew on as well.

[jpkeller55]

Hey Adam,

Yes, Responses are also a Memo field. However, blQuestResponseSets_SHOW and tblQuestResponseSets_HIDE are Text fields while Sequence and resporder are numbers. Do I need all the fields to be Memo to get this to work right?

[weekend00]

When I tried union all in my testing table, I could get whole memo field.

[jpkeller55]

That is interesting. When I remove all the fields except the Sequence, Question, and Response from the query and run the UNION ALL query, it is still truncating the Question. I see were Allen Browne suggests the UNION ALL as the solution as well. There obviously is something else going on here with my data or query that I am not catching.

[ajetrumpet]

Hey Adam,

Yes, Responses are also a Memo field. However, blQuestResponseSets_SHOW and tblQuestResponseSets_HIDE are Text fields while Sequence and resporder are numbers. Do I need all the fields to be Memo to get this to work right?

Jim,

If you read Allen's page, he mentions aggegration. I've noticed that you've got a 'group by' in the first qry and an 'order by' in the second one. I would certainly guess that this combo is causing something.

I'm not exactly sure what weekend is testing, but if he is using similar data as you are, something might be corrupt with the query, but I do doubt that is the case...

do me a favor and run some of the following queries and tell me if the memos are being cut:

don't use a 'group by':

select 1 as temp,first([Order]) as Sequence, Question, 

""as Response,"" as RespOrder,"" as tblQuestResponseSets_SHOW,

"" as tblQuestResponseSets_HIDE from tblTempCounsel 

UNION select 2 as temp,[Order],"",Response, resporder, 

tblQuestResponseSets_SHOW, tblQuestResponseSets_HIDE 

from tblTempCounsel ORDER BY [Sequence], temp, RespOrder;

don't use any aggregations or indexing clauses:

select 1 as temp,first([Order]) as Sequence, Question, 

""as Response,"" as RespOrder,"" as tblQuestResponseSets_SHOW,

"" as tblQuestResponseSets_HIDE from tblTempCounsel 

UNION select 2 as temp,[Order],"",Response, resporder, 

tblQuestResponseSets_SHOW, tblQuestResponseSets_HIDE 

from tblTempCounsel

take out all the functions:

select [Order] as Sequence, Question, 

""as Response,"" as RespOrder,"" as tblQuestResponseSets_SHOW,

"" as tblQuestResponseSets_HIDE from tblTempCounsel 

UNION select [Order],"",Response, resporder, 

tblQuestResponseSets_SHOW, tblQuestResponseSets_HIDE 

from tblTempCounsel

[weekend00]

I did not have group by.

Group by a memo field does truncate it. But can group by other fields and use first(memofield) instead.

[jpkeller55]

Adam,

I tried each of your queries. Got error messages on the first two and the third ran but still had truncated values for Questions. See attached screen shots.

[ajetrumpet]

Adam,

I tried each of your queries. Got error messages on the first two and the third ran but still had truncated values for Questions. See attached screen shots.

sorry about those errors. my fault. I posted those queries quickly. why don't you upload the file? We both can take a look and see what's going on.

I don't have an answer without looking at it man. I can't wrap the head around it without a visual. I wouldn't know why the 3rd query is still trucating...i wouldn't think it should at all, but you never know with MS access. I'm personally not doing much until 4pm, so I could get back to you pretty quick if you get it up here in time.

[weekend00]

Did you use UNION ALL in the third try?

eh, Aje did not put UNION ALL in any of his three samples.

Based on what I know, UNION ALL is a must.

[jpkeller55]

OK, here is an example of the DB...stripped down for confidentiality purposes.

[ajetrumpet]

OK, here is an example of the DB...stripped down for confidentiality purposes.

why are you unionizing a table with itself Jim? That may very well be the problem...

[jpkeller55]

I want the question to appear only one time and the responses to show on seperate lines. The query is intended as a means to review a questionaire and we are trying to make it look as much like a questionaire as possible to make review easier. I am open to other suggestions or techniques to accomplish but not sure how to do that.