Union query truncates data

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Hi

I am using an union query for a report. The union query truncates data from
a memo field to 255 characters. How do I overcome this?

Thanks in advance
RIchard
 
Hello

I used UNION ALL and the data is not truncated. But duplicate data is
printed. For e.g if the data is "good" and the next person's is also "good",
I just want to show "good" once. Therefore the usage of union query. But
this way the field gets tuncated if it is over 255 characters.

How do I overcome this issue? Thanks in advance

Richard
 
Richard said:
Hello

I used UNION ALL and the data is not truncated. But duplicate data is
printed. For e.g if the data is "good" and the next person's is also
"good", I just want to show "good" once. Therefore the usage of union
query. But this way the field gets tuncated if it is over 255
characters.

How do I overcome this issue? Thanks in advance
Click to expand...

You need two queries. The first uses UNION and omits the memo field. This gets
rid of duplicate records. Then you use that query as one input into another
query along with your original tables where you can add the memo field back in.
 
Thanks Rick, let me try it out and see

Richard


Rick Brandt said:
You need two queries. The first uses UNION and omits the memo field. This gets
rid of duplicate records. Then you use that query as one input into another
query along with your original tables where you can add the memo field back in.
Click to expand...
 
Hi Rick

There might be a chance that the memo field have duplicates. If I am not
wrong, then duplicates will be shown.

Richard
 
Richard said:
Hi Rick

There might be a chance that the memo field have duplicates. If I am
not wrong, then duplicates will be shown.
Click to expand...

Your first (UNION) query could include the memo. That would avoid duplicates on
the first 255 characters. Your second query can still add back in the full
untruncated memo field. Then just don't use the truncated one in the second
query's output.

If you have memo fields that are the same on the first 255 characters and then
differentiate afterwards you might be SOL. Unless you want to get real nasty
and start including things like...

Mid(MemoField, 256, 512)

in your UNION.
 
Hi Rick

Actually there is only one memo field. That's the reason for using union
query.

So would it still work.

Thanks in advance for your time

Richard
 
Richard said:
Hi Rick

Actually there is only one memo field. That's the reason for using
union query.
Click to expand...

I understand, but you can have it in both the union query, and then add it again
in the second query. The union query will truncate at 255 characters and
eliminate duplicates on those first 255 characters as well (as other fields you
include). The second query can add the same memo field back in only this time
it won't be truncated providing you don't do anything to the design of the
second query that would cause that.
 
HI Rick

Didn't manage to work it out using the query but I manage to do it at the
report stage.

Many thanks for your time and help. Appreciate it.

Richard
 
Back
Top