Strange sorting behaviour in forms

[J. Goddard]

Hi All - my turn to ASK a question.


I have an application in which some of the forms contain a subform in
datasheet view. It is set up so that users can hide/unhide columns they
wish to see at any given time.

They can also sort the data on any of the columns in the datasheet,
using a custom menu bar, with the appropriate MS toolbar items for
sorting and filtering.

On occasion, they want to sort on two adjacent columns (let's call them
Column A and Column B), i.e. sort on Column A and then by Column B
within Column A - pretty standard stuff.

Column A is to the left of Column B; when they (I) select the two
columns, and click "Sort Descending", the result is that Column A is
sorted fine, but Column B is only partially sorted within Column A; the
higher values within Column B are more-or-less at the top of their
groupings within Column A, but lower ones are mixed in, something like this:

95-95-95-74-74-95-95-95-74-74-74-68-68-74-68-68-55-55-68-68-55...

The data in Columns A and B are colour coded with conditional formatting.

The data does have a filter applied, but doing a Remove Filter/Sort
doesn't help; more records are shown, but still sorted wrong.

when selecting the columns, selecting from left-to-right or
right-to-left and then sorting makes no difference.

Now, here's the *really* strange part:

If I interchange the positions of columns A and B (so now B is on the
left), and then switch them back again - the Sorting is perfect!!

Has anyone ever seen this sort of behaviour, and is there any remedy?

I can post screen snapshots if it will help.

Thanks

John G.

 

[J. Goddard]

Sorry - I forgot to mention - Access 2000.

JG

 

[Wayne-I-M]

Hi

Strange one that ??

Have you set the field property (column B) to Indexed Yes - allow duplicates

Just a thought

 

[J. Goddard]

Good evening all -

I found the source of the strange sorting. Even though the columns were
adjacent on the screen, there were two hidden columns between them (this
was deliberate - users can choose what is displayed). So, when the sort
was applied, it used all 4, even though only 2 were visible. When I
sorted with all 4 visible, I saw that the sorting was in fact being done
correctly.

The fix was to reorder the controls in the form design, which is not
done by moving them around on the form (as one might intuitively think),
rather the tab order has to be changed to the order the columns are to
be (initially) displayed in datasheet view.

John