UNION query truncating MEMO column !

[Guest]

I have a union query that unions two other queries. I checked the output of
the other queries and my memo column is intact. After the union query my memo
column is truncated. How do I fix this? Hope someone can offer a solution as
my users are complaining their data is missing.

 

[Guest]

I ran into this problem a while back. I think the way I worked around it was
to wrap the memo field in a cstr( ) function.

Dale

 

[Guest]

No that's not the problem, the problem is that SELECT DISTINCT or UNION in a
query truncates the memo columns.

 

[Guest]

Understood. Have you tried what I indicated?

As I recall, converting the memo to a string using cStr seemed to resolve
the problem. I don't have any data to test it on at the moment.

Dale

 

[Marshall Barton]

I have a union query that unions two other queries. I checked the output of
the other queries and my memo column is intact. After the union query my memo
column is truncated. How do I fix this? Hope someone can offer a solution as
my users are complaining their data is missing.

Can you use UNION ALL?

 

[Ken Snell \(MVP\)]

If you use UNION ALL as the union connector, no truncation will occur.

 

[Allen Browne]

Marsh & Ken have given the answer.

If you want to know why the truncation occurs, and why UNION ALL solves it,
see:
Truncation of Memo fields
at:
http://allenbrowne.com/ser-63.html

 

[DAVID]

Marsh & Ken have given the answer.

If you want to know why the truncation occurs, and why UNION ALL solves
it, see:
Truncation of Memo fields
at:
http://allenbrowne.com/ser-63.html

Allen, I don't see anything on that page referencing the memo query
grouping bug:

http://groups.google.com/group/microsoft.public.access.queries/browse_frm/thread/f88b17aa13908980

http://groups.google.com/group/micr..._frm/thread/8cc72d860cbe1de8/1279acc40d0d6855

That was still a problem with A2K3 SR7, I haven't tried it since.

(david)

 

[Allen Browne]

As the article explains,
Access truncates the memo if you ask it to process the data
based on the memo: aggregating, de-duplicating, formatting,
and so on.

Later:
A UNION query combines values from different tables, and
de-duplicates them. This means a comparing the memo field,
resulting in truncation.

The UNION ALL does not perform the de-dupliction. Since JET is not required
to compare the values of the memo fields, it is free to return the entire
field.

 

[DAVID]

Allen, read the references I provided.

There is a bug that corrupts data, returning
garbage (probably memo pointer value instead
of data), when you use a memo value in a
grouping query sourced from more than one table.

http://groups.google.com/group/microsoft.public.access.queries/browse_frm/thread/f88b17aa13908980

http://groups.google.com/group/micr..._frm/thread/8cc72d860cbe1de8/1279acc40d0d6855

(david)

 

[Allen Browne]

David, thank you for your patient reply.

Rushing out the door, I had not read your message correctly. I had forgotten
about the issue Peter Miller raised, because it requires the combination of
2 things I never do:
- grouping by a memo field;
- joining on unindexed fields.

Following your reminder, I have created a new page to list this bug:
http://allenbrowne.com/bug-18.html