Report loses sorting criteria of underlying query

[John S. Ford, MD]

I have a form with an embedded subform. The underlying DataSource for the
subform is a query. This same query is the underlying DataSource for a
report generated from the form. The problem is, the records called up by
the subform are appropriately sorted by the underlying query but when the
REPORT is generated, those SAME records are UNSORTED.

The report selects the same records as the form and subform but they just
aren't sorted (ie. the report lists the records in the original entry order
rather than the sorting criteria which happens to be based on LastName,
FirstName).

Can anyone suggest some reasons this might occur? This has me stumped.

John

 

[Allen Browne]

That's correct. Reports do do not reliably follow the sort order you specify
in their source query.

Instead, use the Sorting and Grouping box (View menu, from report design
view), to specify the field(s) you wish to sort by.

 

[John S. Ford, MD]

Allen,

Your trick worked. My question is why? Doesn't this represent a flaw in MS
Access 97 (which is what I'm working with now) and subsequent versions of
Access? Is this a problem Microsoft can't correct or is this behavior
"documented" by Microsoft? If this is a bug then it seems a pretty serious
bug!

John

 

[Rick Brandt]

Allen,

Your trick worked. My question is why? Doesn't this represent a flaw in MS
Access 97 (which is what I'm working with now) and subsequent versions of
Access? Is this a problem Microsoft can't correct or is this behavior
"documented" by Microsoft? If this is a bug then it seems a pretty serious
bug!

It might not be "desired" behavior (by some), but it has always been that way.
Why would you not expect to use properties of a report to control the sorting of
that report? Seems perfectly logical to me.

 

[Albert D. Kallal]

Allen,

Your trick worked. My question is why?

As mentioned, ms-access has always worked this way. The simple answer is
that for all reports, you simply must specify the sorting and grouping in
the report. Often, people base a reports on a tables, and thus there is no
order. And, another advantage is that you can build 5 reports, and base them
all on the same query, or same table, but yet have different sorting and
grouping. It can eliminate the need for having to build a zillion quires.

Thus, you can bring up a report in design mode..and set the sort..and not
worry about the datasouce order. It is definably a catch 22 situation here,
and there are a good deal of pros and cons as to the design decision made
here. However, when all things are considered, it is/was a reasonable design
decision.

So, for rerpots, the simple one rule to remember is that you set the order
in the reprot..and not worry about anything else.

 

[Marshall Barton]

No bug here John. This is a logical consequence of the
Sorting and Grouping report feature. Think about it, if you
specify a sort order in Sorting and grouping, it should be
expected for that to take precedence over any other sorting.
Even if you only specify a group in Sorting and Grouping,
the report **must** be sorted by that field for the grouping
feature to do what you asked it to do.

If you do not specify anything in Sorting and Grouping, the
report's OrderBy (and OrderByOn) property is next in line in
determining how to sort the report. If that too is
unspecified, then the record source query's ORDER BY clause
will not be overridden.

The hidden gotcha in using a query's ORDER BY clause (and
the reason most people will tell you to avoid it, aside from
localization) is that you may get your report working just
the way you want it. Great ... until some time in the
future when you decide you need a group. All of a sudden
your sorting goes out the window. If you had originally
specified the sort in Sorting and Grouping, it would be
crystal clear what was happening and all confusion would be
eliminated.

 

[John S. Ford, MD]

Ok guys, your points all make sense. It just seems to my (not terribly
smart) way of thinking that if that report's property ISN'T set, the default
should be to defer to the report's underlying query. The underlying query
is certainly used to SELECT the records displayed. Why not the sort order?
Of course Bill Gates wisely never solicited MY opinion.

John

 

[Tom Lake]

Ok guys, your points all make sense. It just seems to my (not terribly
smart) way of thinking that if that report's property ISN'T set, the
default
should be to defer to the report's underlying query. The underlying query
is certainly used to SELECT the records displayed. Why not the sort
order?
Of course Bill Gates wisely never solicited MY opinion.

John

dBase does that. If the query specifies a sort order, the report follows
it. I don't know why Access can't do the same.

Tom Lake

 

[Marshall Barton]

Ok guys, your points all make sense. It just seems to my (not terribly
smart) way of thinking that if that report's property ISN'T set, the default
should be to defer to the report's underlying query. The underlying query
is certainly used to SELECT the records displayed. Why not the sort order?
Of course Bill Gates wisely never solicited MY opinion.

As I tried to explain before, John, AFAIK, it does do that.
What have you got that demonstrates otherwise?

 

[Albert D. Kallal]

dBase does that. If the query specifies a sort order, the report follows
it. I don't know why Access can't do the same.

Tom Lake

The original poster is talking about sub-reports....and dBase has no such
feature.

However, access DOES FOLLOW the query order if you do NOT set it in the
reports.

And, in fact, the sub-report does also follow the query order. If the OP is
saying that the order in the sub-report is based on a query with the order
set, and NO settings (grouping/sort) are set in the report, then we
defiantly have some problem here!!
The sub-report should respect the query order....assuming no sorting and
grouping is set in that sub-report.

The only reason why I can think that the OP is having a problem is that the
sub-report is in fact NOT based on a query where the order is set, or the
sub-report has the sort set, or in fact the sub-report is a form, and the
order by is being saved with the form.

The OP needs to check the data sources used for the sub-report, and also
check if the "order by" setting has been saved.

The issue here is that if you set anything in the report...it over rides the
query order by. So, the "sort" used in the report is not cumulative...but
yes, in fact it completely ignores the query sort order.

However, if no order is set at all..then the query should in fact set the
order. If it is not..then something else is a miss here...