ORDER BY involving memo field bug??

[Guest]

I have a query with an ORDER BY clause like the following

ORDER BY F1, F2, F3, F4

field F2 is a memo field
I'm finding that sometimes where F1, F2 and F3 are all the same, the output
is not sorted correctly by F3 and F4

Is this a bug, does anyone know?

 

[Michel Walsh]

Hi,


In the help file, under "Access specifications", you will find that the sort
limit is fixed to 255 char in one or more fields. If the cumulative size,
after have read the memo, make for 255 char or more, f3 and f4 would be
incorrectly sorted, indeed.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I think that was it. It did not look like the actual data was over 255 chars
but when I changed my sort clause to say LEFT$(...,100) on the memo field,
the sort sequence corrected itself.

-David

 

[Marshall Barton]

I have a query with an ORDER BY clause like the following

ORDER BY F1, F2, F3, F4

field F2 is a memo field
I'm finding that sometimes where F1, F2 and F3 are all the same, the output
is not sorted correctly by F3 and F4

Is this a bug, does anyone know?

Not a bug as Michel explains, but the easy workaround is to
do something like:

ORDER BY F1, Left(F2, 100), F3, F4

OTOH, I'm not sure what you mean by "F1, F2 and F3 are all
the same"

 

[david epsom dot com dot au]

Note that there is indeed a bug when grouping by
memo fields (including union queries), which has
the effect of putting garbage data into the fields.

That bug shows up when you use multiple tables. It
is avoided by explicitly using only the first 255
characters of the field.

(david)