By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,233 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Truncated memo fields when using SELECT DISTINCT

P: n/a
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?
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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 <th**************@yahoo.co.uk>
wrote:
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?


Nov 12 '05 #2

P: n/a
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.

"skinnybloke" <th**************@yahoo.co.uk> wrote in message
news:8g********************************@4ax.com...
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?

Nov 12 '05 #3

P: n/a
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!
Nov 12 '05 #4

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:40**********************@freenews.iinet.net.a u:
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.


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
Nov 12 '05 #5

P: n/a
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" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:40**********************@freenews.iinet.net.a u:
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.


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.

Nov 12 '05 #6

P: n/a
On Fri, 30 Apr 2004 09:34:27 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:
Yes, you're right. DISTINCT does fail.

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


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

P: n/a
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" <no****@nospam.nospam> wrote in message
news:5f********************************@4ax.com...
On Fri, 30 Apr 2004 09:34:27 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:
Yes, you're right. DISTINCT does fail.

GROUP BY truncates, but using First() lets you return the whole thing.
I didn't think that worked, but I don't remember when I last tried. Have

you verified that this works?

Nov 12 '05 #8

P: n/a
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!
Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.