Sort order is wrong

[Bill]

I have a report whose RecordSource is a
query (really simple) that clearly defines
the sort order as ascending on the field
"LastName". When I run the query, the
resulting recordset is exactly as specified,
but when I run the report, the sorted order
seems to be random.

There's nothing in the report's module that
addresses any of the properties that affects
sorting, so I'm at a loss as to what might
be causing the problem.

Any suggestions?

Thanks,
Bill

 

[Bill]

Setting the report's OrderBy to LastName fixed
the problem.

Strange that the filtering specified by the query
applied correctly, but the sort specifications
didn't.

Thanks,
Bill

 

[Marshall Barton]

I have a report whose RecordSource is a
query (really simple) that clearly defines
the sort order as ascending on the field
"LastName". When I run the query, the
resulting recordset is exactly as specified,
but when I run the report, the sorted order
seems to be random.

There's nothing in the report's module that
addresses any of the properties that affects
sorting, so I'm at a loss as to what might
be causing the problem.

Report sorting should be specified in the Sorting and
Grouping Windoe (View menu).

 

[Bill]

Thanks Marsh, that's very useful knowledge to have. It
turns out that in general I've been successful purely by
dumb luck. Those reports that I have that use grouping
were properly configured for sorting because the wizard
prompted (provides for) the sort specification. It never
occurred to me that grouping is "the boss". Those without
groupings had the Order By property set properly. I'm
not sure how this current case fell through the cracks.

Anyway, thanks much.
Bill

No, that is not strange. Filtering and sorting are
completely separate operations.

Reports are complex objects and it's important to understand
how a query's sort, the OrderBy property and Sorting and
Grouping interact. Without going into the minutia, the
query's sorting is done by the query, no problem. However,
except for trivial reports, another internally generated
query is used to organize a report's data for aggregate
functions and grouping, which (necessarily) obliterates the
report's record source query's sorting. The actual sorting
in a report is determined by first using all entries in
Sorting and Grouping and then the OrderBy property is added
to the end of the list of field/expressions to sort.

Your results imply that you do not have anything in Sorting
and Grouping, but that your report uses an aggregate
function (Count, Sum,etc) in the report's header or footer
section. Just be aware that most of the time OrderBy is not
the answer to these issues and Sorting and Grouping is the
boss of sorting.
--
Marsh
MVP [MS Access]

Setting the report's OrderBy to LastName fixed
the problem.

Strange that the filtering specified by the query
applied correctly, but the sort specifications
didn't.


"Marshall Barton" wrote

 

[Marshall Barton]

No, that is not strange. Filtering and sorting are
completely separate operations.

Reports are complex objects and it's important to understand
how a query's sort, the OrderBy property and Sorting and
Grouping interact. Without going into the minutia, the
query's sorting is done by the query, no problem. However,
except for trivial reports, another internally generated
query is used to organize a report's data for aggregate
functions and grouping, which (necessarily) obliterates the
report's record source query's sorting. The actual sorting
in a report is determined by first using all entries in
Sorting and Grouping and then the OrderBy property is added
to the end of the list of field/expressions to sort.

Your results imply that you do not have anything in Sorting
and Grouping, but that your report uses an aggregate
function (Count, Sum,etc) in the report's header or footer
section. Just be aware that most of the time OrderBy is not
the answer to these issues and Sorting and Grouping is the
boss of sorting.