473,322 Members | 1,425 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Truncated memo fields when using SELECT DISTINCT

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
8 8167
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
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
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
"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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: skinnybloke | last post by:
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...
9
by: skinnybloke | last post by:
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...
2
by: Robert | last post by:
When you use an aggregate function in a SELECT statement, you cannot specify any fields you want they way you usually can in a SELECT statement. Only fields that are part of the GROUP BY clause...
3
by: Laurie | last post by:
I am using Automation in Access 2003 to open a Word Document and fill in some values using bookmarks. It all works perfectly except for one section. I am filling in some values in a previously...
2
by: Charlie | last post by:
Hi: I have a stored proc that returns an XML doc. Testing it in Query Analyzer, it looks fine. However consuming it in C# application truncates xml string when result is converted to string...
3
lwwhite
by: lwwhite | last post by:
I've got a a datasheet subform that gets its data from a SELECT DISTINCT query. The data is not editable. When I remove the DISTINCT qualifier from the query, the data is editable, but of course I...
5
by: bitsnbytes64 | last post by:
Hi, I have a test form with: * textbox txtIXO_NR bound to column IXO_NR through the linked SQL Server 2005 table TXOCTC (link name "dbo_TXOCTC") * an unbound textbox which should display the...
0
by: data monkey | last post by:
My company's web application uses mySQL databases. I have linked these tables (Read only) into Access 2007 so I can create queries and reports. Now I need to extract some of the content from these...
8
by: Jimmy Jones | last post by:
First of all - all answers that I could find via Google do not give me any explicit info as to how to handle this problem. So please - do not paste the following link for the hundreth time: ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.