Memo Field data truncated in query

[Joel]

TIA:

I have a multi-table query; a child table has a memo
field with lots of text. A multi-table query with parent
and child table fields with no criteria returns the ALL
of the memo data. If any criteria is set for fields
other than the memo field, the query returns the memo
data truncated at 255 characters!!!

Access 2002, searched knowledge base and found nothing
specific to this issue. Other issues about 255ch
truncation.

Anyone know about this issue? Any fix?? Some other
setting causing the truncation??

Thanks!!!!
Joel

 

[Allen Browne]

Several possible causes. The most common is that your query is performing
some kind of aggregation which includes the memo field, e.g. if the query
contains a GROUP BY clause that includes the memo field, or if there is a
DISTINCT in the predicate.

If that does not give you a useful lead, post the SQL statement from the
query (by selecting SQL View from the view menu in query design).

 

[Joel]

Alan:

Thanks, I have the unique values set to yes which creates
the Distinct SQL clause. Is this true with all databases
or just Access to limit Group By or Distinct Memo data to
255 characters?
Thanks,
Joel

 

[Allen Browne]

If you ask Access to distinguish the records based on the value in memo
fields, it examines only the first 255 characters. If you understand what is
actually involved in string comparisons that potentially involve tens of
thousands of characters per record, you can appreciate that design choice.

One work around with a Totals query is to use First instead of Group By
under the memo field. This means that Access does not have to group by the
memo field, and can just return the first matching memo field for the
record, so it is free to return the entire memo field.

Another workaround is to create a query that performs the DISTINCT
operations you want without the memo field, and then use it as the input to
another query that combines the first one and the table, retriving the value
of the memo without doing further aggregation.

HTH