Sorting doesn't work?

  • Thread starter Thread starter Marvin Cohen
  • Start date Start date
M

Marvin Cohen

If I have an Access Database with about 15 fields, and I make a query
which basically shows all fields but also sorts by all 15, I find that
the sort fails sometimes. Sometimes a record that should come first
comes second.
In design view, I show the fields from left to right, and I click on
'sort ascending' for each one.
Is there a limit on how many fields can be sorted?
Thanks,
Marvin
 
Most of the limitations of Access can be found in the help file (search for
'specifications'). There are no limitations listed there on the number of
fields in an ORDER BY clause, and if there is a limitation, I'd be surprised
if it were not higher than 15.

The most common cause of queries not being sorted in the way you may expect
is the difference in the way that text and numeric (and date) fields are
sorted. If you could post the SQL for the query, and list the data type of
each field used in the ORDER BY clause, and an example of how the records
are sorted and how you expected them to be sorted, it might be easier to see
what the problem is.
 
Brendan Reynolds \(MVP\) said:
Most of the limitations of Access can be found in the help file (search for
'specifications'). There are no limitations listed there on the number of
fields in an ORDER BY clause, and if there is a limitation, I'd be surprised
if it were not higher than 15.

The most common cause of queries not being sorted in the way you may expect
is the difference in the way that text and numeric (and date) fields are
sorted. If you could post the SQL for the query, and list the data type of
each field used in the ORDER BY clause, and an example of how the records
are sorted and how you expected them to be sorted, it might be easier to see
what the problem is.
Click to expand...
Hello Brendan,
The sql statement that I used was:
-----------------
SELECT GroupByInputs_9W9.field1, GroupByInputs_9W9.field2,
GroupByInputs_9W9.field3, GroupByInputs_9W9.field4,
GroupByInputs_9W9.field5, GroupByInputs_9W9.SPDiff,
GroupByInputs_9W9.Slope2, GroupByInputs_9W9.Slope3,
GroupByInputs_9W9.Slope4, GroupByInputs_9W9.Slope5
FROM GroupByInputs_9W9
ORDER BY GroupByInputs_9W9.field1, GroupByInputs_9W9.field2,
GroupByInputs_9W9.field3, GroupByInputs_9W9.field4,
GroupByInputs_9W9.field5, GroupByInputs_9W9.SPDiff,
GroupByInputs_9W9.Slope2, GroupByInputs_9W9.Slope3,
GroupByInputs_9W9.Slope4, GroupByInputs_9W9.Slope5;
------------------
all fields were text. Samples of content were "BIN0001" and BIN002".
I showed the mis-sorting to another co-worker, and he can't figure it
out either. The field that was out of order was 'Slope5'. If you
like, I can send you the database and you can take a look at it.
Thanks.
Marvin
 
Brendan,
I made a mistake in my last posting. I'm comparing strings such as:
BIN0002
BIN0001
etc.
I expect BIN0001 to come before BIN0002, but thats not happening.
Both have the same number of leading zeros.
-- Marvin
 
I can't see anything obvious. What I would suggest is to create a new query
that sorts only by that one field. Does it sort as expected? If not, the
problem is probably something about the data in that field - a leading
space, a 1 (the digit one) instead of an I in 'BIN' or a capital O instead
of a zero, something like that.

If the query that sorts by only the one field does sort as expected, then
the next step would be to add each of the other fields to the ORDER BY
clause one at a time. At what point does the query stop sorting as expected?
That will have narrowed down the problem to a specific field.
 
I found out why sorting in Access does not always work. When the sum
of the lengths of the fields being sorted exceeds 255, Access is not
designed to cope.
You can see this by going to HELP and searching for SPECIFICATIONS in
Access HELP.
 
Back
Top