Unique Values Query Text Truncation (2003 (11.5614.5703))

Topic

New Reply

I’ve created a query based on a table with a memo field. Some of the memo fields are >255 characters long. If I specify UniqueValues = Yes (DISTINCT), the query result for the memo field is truncated to the portion beyond 255 characters (the first 255 characters are lost). (Memos with <255 characters look okay.) This seems strange to me. Should it be expected? I read somewhere that Access uses only the first 255 characters when comparing strings, but this leading truncation shouldn't be a conseqence of that, should it?

If I specify UniqueRecords = Yes (DISTINCTROW) or if both are = No, this truncation does not occur.

Any ideas what's causing this? Is there a way to avoid it while still using DISTINCT in the SQL?

Reply | Quote

Replies

Using SELECT DISTINCT is basically the same as a totals query with GROUP BY on all fields. The Jet Engine cannot group on memo fields; therefore they are truncated to text fields (max length 255 characters). As far as I know, this is an intrinsic restriction of the Jet Engine.

See GROUP BY Clause.

Reply | Quote

Thanks for the reference, Hans. The rationale for the truncation makes more sense now, although I still find it strange that it is the excess-beyond-255-characters that is in the query results, not the first 255 (or less) characters. Also, it seems that SELECT DISTINCTROW would exhibit the same problem since it also has to do comparisons between memo fields..

Reply | Quote

Thanks for the reference, Hans. The rationale for the truncation makes more sense now, although I still find it strange that it is the excess-beyond-255-characters that is in the query results, not the first 255 (or less) characters. Also, it seems that SELECT DISTINCTROW would exhibit the same problem since it also has to do comparisons between memo fields..

Reply | Quote

Using SELECT DISTINCT is basically the same as a totals query with GROUP BY on all fields. The Jet Engine cannot group on memo fields; therefore they are truncated to text fields (max length 255 characters). As far as I know, this is an intrinsic restriction of the Jet Engine.

See GROUP BY Clause.

Reply | Quote