Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unique Values Query Text Truncation (2003 (11.5614.5703))

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unique Values Query Text Truncation (2003 (11.5614.5703))

    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.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Values Query Text Truncation (2003 (11.5614.5703))

    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..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •