Potential Bug in Access 2003

  • Thread starter Thread starter Simon Purdy
  • Start date Start date
S

Simon Purdy

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
 
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.
 
Simon Purdy said:
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).
Click to expand...
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
 
Simon Purdy said:
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).
Click to expand...
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
 
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
 
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://groups.google.com/groups?hl=...Search&meta=group%3Dmicrosoft.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)
 
Back
Top