union query cuts memo fields

[Guest]

i must have a union query which includes a memo field, but the union query
cuts it off. what do i do?

 

[Douglas J. Steele]

By default, union queries eliminate duplicate rows, and in order to do that,
they can't work with memo fields.

If you use UNION ALL rather than UNION, the query won't eliminate duplicate
rows, and should leave the memo fields alone.

(This assumes, of course, that you're not using the memo fields in the ORDER
BY clause, nor formatting them in any way)

 

[Allen Browne]

Use:
UNION ALL
instead of:
UNION

By default, a UNION query will deduplicate the records of the various
SELECTs. To do this, Access must compare the memo fields, and this results
in truncation. UNION ALL does not deduplicate, so Access is free to return
the whole memo.

 

[Guest]

thanks douglas and thanks allen

i am using union all:

SELECT [col1],[col2],[col3]
FROM [tableA]
UNION ALL SELECT [col1],[col2],[col3]
FROM [tableB]

but my memo fields get truncated
actually col3 of tableB is the only memo field, its tableA cousin is text

please help

 

[Douglas J. Steele]

You could try reversing the order of the SELECT statements so that the Memo
field appears in the first one:

SELECT [col1],[col2],[col3]
FROM [tableB]
UNION ALL
SELECT [col1],[col2],[col3]
FROM [tableA]

If that doesn't work, I think you're out of luck.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

thanks douglas and thanks allen

i am using union all:

SELECT [col1],[col2],[col3]
FROM [tableA]
UNION ALL SELECT [col1],[col2],[col3]
FROM [tableB]

but my memo fields get truncated
actually col3 of tableB is the only memo field, its tableA cousin is text

please help

--
Thankfully, YisMan

Use:
UNION ALL
instead of:
UNION

By default, a UNION query will deduplicate the records of the various
SELECTs. To do this, Access must compare the memo fields, and this
results
in truncation. UNION ALL does not deduplicate, so Access is free to
return
the whole memo.