Union query truncates data

[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

 

[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

 

[Rick Brandt]

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

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.

 

[Richard]

Thanks Rick, let me try it out and see

Richard

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.

 

[Richard]

Hi Rick

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

Richard

 

[Rick Brandt]

Hi Rick

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

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.

 

[Richard]

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

 

[Rick Brandt]

Hi Rick

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

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.

 

[Richard]

Hi Rick

Would the second query be a union query also?

Richard

 

[Richard]

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