Re: Bug? - Group By turning field property from Memo to Text

[Douglas J. Steele]

AFAIK, there's no way to prevent that from happening.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Callaway" <Call...@discussions.microsoft.com> wrote in message
news:63ED72A3-6710-4946...@microsoft.com...
> Interesting thing happening.
>
> Created a flat database in AccessXP (patched as of 6/26/04). Have a field
labeled description with the property of memo. The description field has
more than 255 characters in it.
>
> When I view the table in datasheet view, all the text in the Description
field is displayed. When I create a simple query with only the description
field, the query displays all of the text inside the description field
properly.
>
> However, when I use the Total property for the query (Group By), suddenly
the query cuts off all text in the description field after 255 characters,
almost like it turned the field property into a memo.
>
> Any suggestions for fixing this?
>
>

[John Spencer (MVP)]

A POSSIBLE work around way to handle this is to use First instead of GROUP BY on
the memo field. It will get the first memo that goes with the other criteria.
When Access needs to decide on unique values for a memo field (Group by,
distinct clause) then it truncates the memo field to 255 characters for
comparison purposes. {{{{This gives you a random memo field - anything from the
longest to the shortest to one that is null}}}}

A second way would be to drop the memo field from the query. Then in a new
query based on your group by query, add the memo field back in by adding the
relevant table and joining on the relevant fields. {{{{This can give you
multiple records - one for each memo field even if they are identical}}}} This
may or may not resolve your problem.

A third possibility it to use the string functions and break up the memo field
into 250 character chunks and group on the chunks. This works, but is slow and
depends on you knowing the maximum length of the data stored in the memo field.
{{{{This may very well truncate information in long memo fields and is slow}}}}

SELECT SomeField, Sum(SomeNumberField),
Mid(MemoField,1,250) as Part1,
Mid(MemoField,251,500) as Part2
From SomeTable
GROUP BY SomeField,
Mid(MemoField,1,250) as Part1,
Mid(MemoField,251,500) as Part2

None of those possible solutions may help you.

[david epsom dot com dot au]

Unless this has been fixed, you want to be careful when
grouping by memo field:

--------------
(14 October 2003, SR7 "Access Bug, Grouping by Memo")
there was still a bug when grouping by Memo Fields.

Peter Miller described the bug last year, but it has
apparently existed since 'group by memo field' was
intoduced with Access 2000.

For Peter Miller's sample demonstration:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=5klveucdhu2bd1vaqqh5i9ikjbpb5ao1qm%404ax.com&rnum=5&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3Dmemo%2Bbug%26btnG%3DGoogle%2BSearch%26meta%3Dgroup%253Dmicrosoft.public.access.*

or search for "BUG: Group by memo field in A2k or AXP"

In summary: only group by memo when using only ONE table,
or Use "Left" to select the first 255 characters of the
memo field, and group on that.

Still present in XP:
"Simon Purdy" <Simon...@the-marketing-centre.co.uk> wrote in message
news:Oq476AC...@TK2MSFTNGP09.phx.gbl
------------

(david)