query with memo field cuts off at 255 characters

[dixie]

I have just run into a problem where I have a text box control on a form
that is linked to a memo field in a table. When I type a really long
comment into the field, it allows me to type it and then does save the
entire comment to the table. However, when I use a query which includes
this field to send to a report, the field is truncated at the old text field
limit of 255 characters. I have ascertained that it is happening in the
query, not on the report. Has anyone experiences this before and if so, how
do I overcome it. I am using Access 2000.

dixie

[dixie]

OK, I'll answer my own question after some quick experimenatation. If you
use First or Last and not GroupBy in the query on the memo field it will
return the fully number of characters. Weird, but it works - actually to me
that seems like a bug!

dixie

"dixie" <di****@dogmail.com> wrote in message
news:Sm***************@nnrp1.ozemail.com.au...

I have just run into a problem where I have a text box control on a form
that is linked to a memo field in a table. When I type a really long
comment into the field, it allows me to type it and then does save the
entire comment to the table. However, when I use a query which includes
this field to send to a report, the field is truncated at the old text field limit of 255 characters. I have ascertained that it is happening in the
query, not on the report. Has anyone experiences this before and if so, how do I overcome it. I am using Access 2000.

dixie

[Rick Brandt]

"dixie" <di****@dogmail.com> wrote in message
news:zi***************@nnrp1.ozemail.com.au...

OK, I'll answer my own question after some quick experimenatation. If you use First or Last and not GroupBy in the query on the memo field it will
return the fully number of characters. Weird, but it works - actually to me that seems like a bug!

dixie

Actually it's a feature. Until Access 2000 you couldn't do a GroupBy on a
memo field at all. It just gave you an error indicating that it wasn't
allowed. You could however do a GroupBy on the expression
Left([MemoField,255). The newer versions of Access just starting doing
this for you automatically in the background instead of disallowing the
GroupBy entirely. I do agree though that it was a mistake to not alert the
person creating the query to this fact.

The reason for these limitations is that memo content is not actually
stored in the table. Only a pointer to its location on disk is. This
makes features like sorting and grouping _very_ inefficient because the
query has to use the pointer to go "get" the text, evaluate it, and then
apply the sorting and grouping.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com