Potential Bug in Access 2003

by **Simon Purdy** » Wed, 03 Dec 2003 00:31:42 GMT

Hi

Interesting issue here.
Was working with the following SQL statement in Access 2003:-

SELECT     a.Text
FROM   (SELECT     NOTES.Text
                       FROM        PEOPLE INNER JOIN  NOTES ON
PEOPLE.Client_Id = NOTES.Client_Id AND PEOPLE.Contact_Number =
NOTES.Contact_Number) a
GROUP BY a.Text

NOTES.Text is a Memo field as it may store more than 255 Characters.

On running the query the Text field returns 2 unicode characters, it is my
belief that this is a 32 bit address for where the Note is stored. If the
Join is removed from the internal Select statement the returned Text field
is expected.

The above statement was cut down from a much longer statement in the form
it's used in.

Anybody else seen this and is there any solutions (I am unable to change the
structure of the SQL Statement unfortunately).

Many Thanks.

Si

by **Douglas J. Steele** » Wed, 03 Dec 2003 02:45:21 GMT

I don't believe it's possible to use Memo fields in GROUP BY statements.

I'd also suggest renaming the field to something other than Text: I suspect
that's a reserved word.

-- 
Doug Steele, Microsoft Access MVP
 http://www.**--****.com/ 
(No private e-mails, please)







the

by **Gary Walter** » Wed, 03 Dec 2003 07:39:40 GMT

"Simon Purdy" < XXXX@XXXXX.COM > wrote
Hi Si,

In addition to Douglas's sage advice,

1) would you mind verifying that Client_Id and Contact_Number
   fields are indexed in both tables?

2)would you mind trying

SELECT a.SortText, First(a.FullText) As FullMemo
FROM  (SELECT   Left(NOTES.[Text],255) As SortText,
                                NOTES.[Text] As FullText
               FROM  PEOPLE INNER JOIN  NOTES ON
              PEOPLE.Client_Id = NOTES.Client_Id
             AND PEOPLE.Contact_Number = NOTES.Contact_Number) a
GROUP BY a.SortText;

Thanks,

Gary Walter

by **Gary Walter** » Wed, 03 Dec 2003 07:45:45 GMT

"Simon Purdy" < XXXX@XXXXX.COM > wrote
Hi Si,

In addition to Douglas's sage advice,

1) would you mind verifying that Client_Id and Contact_Number
   fields are indexed in both tables?

2)would you mind trying

SELECT a.SortText, First(a.FullText) As FullMemo
FROM  (SELECT   Left(NOTES.[Text],255) As SortText,
                                NOTES.[Text] As FullText
               FROM  PEOPLE INNER JOIN  NOTES ON
              PEOPLE.Client_Id = NOTES.Client_Id
             AND PEOPLE.Contact_Number = NOTES.Contact_Number) a
GROUP BY a.SortText;

Thanks,

Gary Walter

by **Simon Purdy** » Wed, 03 Dec 2003 17:49:37 GMT

Thanks

You were correct about the not being able to group by memo fields.

The SQL code is autogenerated hence the rigid structure, I now have to
modify the code that generates the SQL.

Again, many thanks.

Si




suspect



my
the
field
form

by **david epsom dot com dot au** » Wed, 03 Dec 2003 18:19:37 GMT

Yes, last I checked (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://www.**--****.com/ %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.

(david)






the

Potential Bug in Access 2003

Potential Bug in Access 2003

Jump to:

Next

Potential Bug in Access 2003

Re: Potential Bug in Access 2003

Re: Potential Bug in Access 2003

Re: Potential Bug in Access 2003

Re: Potential Bug in Access 2003

Re: Potential Bug in Access 2003

Similar Threads:

Who is online