[AccessD] Learned something else new today

I wrote an app for an insurance call center.  The head tech cheese was
trying to get a bunch of report queries to work "easier" which were written
by a young lady they assigned that task.  She had been pulling a result set
with dozens of event records for each claim then "cutting and pasting" the
right ones into excel.  JohnS had turned it into a group by and successfully
caused it to pull just the result set he wanted.  However a memo field was
displaying a pair of Chinese characters (literally).  Very strange looking,
and definitely not what we wanted to see.  

It turns out that he had a group by on that field.  Can you guess what it
was doing (or my educated guess anyway)?  It took me a few minutes to figure
it out!!!

As you probably know, memo fields are not stored in the record, but rather a
(32 bit?) pointer to the memo is stored.  The GroupBy was causing the memo
field to be evaluated literally, thus it was taking the POINTER and
displaying (and grouping by) that.  I am also guessing that Access knew that
a memo is supposed to be text so it was doing an implicit cstr() on the
pointer to attempt to coerce the value back to text and to display the value
as a string.  Thus it was displaying Chinese (and other odd) characters.

By changing the GroupBy to a Max (I think anyway) the memo field text
reappeared.

I have never actually seen, or found any way to see the actual pointer to
the memo field out in the memo area but it certainly appears that using a
groupby on that field caused the pointer data itself to be exposed as the
"value" of the memo.

Cool huh?

So if your ever seeing a pair of totally weird characters in a group by
query, see if the field is a memo with a group by under it.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/