Truncated memo fields when using SELECT DISTINCT

[skinnybloke]

Hi - I have a problem with a memo field being truncated to about 255
characters when running a Access 2002 query.

This only seems to happen if I use SELECT DISTINCT. It works ok using
SELECT by itself.

does anyone know why this happens and how to stop it?

[Steve Jorgensen]

Re: Truncated memo fields when using SELECT DISTINCT

Memo field values cannot be sorted or grouped. Select Distinct is converting
the mem field values to Text(255) so it can group them instead of just
failing. This is all quite reasonable since a Memo value is of essentially
unlimited size, and the logic to be able to effectively check for duplicates
would be very complicated.

Usually, I find that when someone is trying to group by or use DISTICT with a
Memo field, that the Memo field data is not part of what needs to be evaluates
to check for duplicates. For instance, it might be looked up from a related
table such that it will be the same for any unique combination of the records
being grouped. In this case, you can use a Group By or Distinct query of the
other data including the foreign key to get the related record that has the
Memo, then make another query that joins the first query to the other table.

On Thu, 29 Apr 2004 15:35:05 +0100, skinnybloke <theoriginalsin 73@yahoo.co.uk>
wrote:
[color=blue]
>Hi - I have a problem with a memo field being truncated to about 255
>characters when running a Access 2002 query.
>
>This only seems to happen if I use SELECT DISTINCT. It works ok using
>SELECT by itself.
>
>does anyone know why this happens and how to stop it?[/color]

[Allen Browne]

Re: Truncated memo fields when using SELECT DISTINCT

Yes, using DISTINCT or GROUP BY on a memo field causes Access to return only
the first 255 characters of a memo, and you cannot change that behavior. If
it did not operate that way, it would potentially have to read tens of
thousands of characters from every record in order to determine whether the
record was distinct or not.

One workaround might be to use a Totals query to group the way you intend,
and use First in the Total row under your memo. This allows Access to work
out the grouping on the other fields, and just grab everything from the
first matching memo field, so it can return the lot.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"skinnyblok e" <theoriginalsin 73@yahoo.co.uk> wrote in message
news:8g42901g0s pk9v57addbij8oe e753tcv5b@4ax.c om...[color=blue]
> Hi - I have a problem with a memo field being truncated to about 255
> characters when running a Access 2002 query.
>
> This only seems to happen if I use SELECT DISTINCT. It works ok using
> SELECT by itself.
>
> does anyone know why this happens and how to stop it?[/color]

[skinnybloke]

Re: Truncated memo fields when using SELECT DISTINCT

Thanks for the replies guys - I will look into your suggestions.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David W. Fenton]

Re: Truncated memo fields when using SELECT DISTINCT

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in
news:40911b83$0 $4321$5a62ac22@ freenews.iinet. net.au:
[color=blue]
> Yes, using DISTINCT or GROUP BY on a memo field causes Access to
> return only the first 255 characters of a memo, and you cannot
> change that behavior.[/color]

Er, which versions of Access truncate the field? All the ones *I*
have fail, and tell you that you can't do it on a memo field.

You can get round it if you have to by processing the memo field to
a fixed length, as in Left(MemoField, 4096), but Steve was right
that you shouldn't really need to do that, either.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Allen Browne]

Re: Truncated memo fields when using SELECT DISTINCT

Yes, you're right. DISTINCT does fail.

GROUP BY truncates, but using First() lets you return the whole thing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:Xns94DAC4D DE929Cdfentonbw aynetinvali@24. 168.128.86...[color=blue]
> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in
> news:40911b83$0 $4321$5a62ac22@ freenews.iinet. net.au:
>[color=green]
> > Yes, using DISTINCT or GROUP BY on a memo field causes Access to
> > return only the first 255 characters of a memo, and you cannot
> > change that behavior.[/color]
>
> Er, which versions of Access truncate the field? All the ones *I*
> have fail, and tell you that you can't do it on a memo field.
>
> You can get round it if you have to by processing the memo field to
> a fixed length, as in Left(MemoField, 4096), but Steve was right
> that you shouldn't really need to do that, either.[/color]

[Steve Jorgensen]

Re: Truncated memo fields when using SELECT DISTINCT

On Fri, 30 Apr 2004 09:34:27 +0800, "Allen Browne"
<AllenBrowne@Se eSig.Invalid> wrote:
[color=blue]
>Yes, you're right. DISTINCT does fail.
>
>GROUP BY truncates, but using First() lets you return the whole thing.[/color]

I didn't think that worked, but I don't remember when I last tried. Have you
verified that this works?

[Allen Browne]

Re: Truncated memo fields when using SELECT DISTINCT

Yep. Doesn't filter on the memo field, but it does return the whole 60,000
characters in the example I tested.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
news:5ff39058t6 k0g8beohd70tv44 ovifl398o@4ax.c om...[color=blue]
> On Fri, 30 Apr 2004 09:34:27 +0800, "Allen Browne"
> <AllenBrowne@Se eSig.Invalid> wrote:
>[color=green]
> >Yes, you're right. DISTINCT does fail.
> >
> >GROUP BY truncates, but using First() lets you return the whole thing.[/color]
>
> I didn't think that worked, but I don't remember when I last tried. Have[/color]
you[color=blue]
> verified that this works?[/color]

[skinnybloke]

Re: Truncated memo fields when using SELECT DISTINCT

Thanks for pointing me in the right direction on this one guys.

I did it as advised by splitting the query into 2 queries. The one I did
the SELECT DISTINCT stuff on and then pulled in the memo fields on the
2nd query with a SELECT statement with the DISTINCT.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!