Report RecordSource

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When you assign a SQL statement to the report RecordSource, and that SQL has
a sort order, are you still required to use the "Sorting And Grouping" to
sort the data ? It seems redundant.

This is the SQL that does not want to sort when the report opens:

SELECT * FROM PurchasesData WHERE ((dtmPurchase Between #10-01-2007# And
#10-27-2007#)) ORDER BY txtType, txtItem;

Thank you.
 
Answers inline:
--

Ken Snell
<MS ACCESS MVP>


BrerGoose said:
When you assign a SQL statement to the report RecordSource, and that SQL
has
a sort order, are you still required to use the "Sorting And Grouping" to
sort the data ?
Click to expand...

Yes. Reports ignore the ORDER BY clause in the RecordSource query.

It seems redundant.
Click to expand...

Perhaps, but that is how ACCESS is designed so that you can have greater
control over the grouping in the report.
 
Ok, that is what I thought, but hoped for an easy way out. Since I can get
to the "Sorting And Grouping" via VBA, it is only a little extra work to do.

Thank you for the fast response.
 
Yes. Reports ignore the ORDER BY clause in the RecordSource query.
Click to expand...

That is not my experience. If you have no assigned Sorting and Grouping,
changing the Order By clause in the RecordSource does change the displayed
sort order.

- Stephen
 
Stephen K. Young said:
That is not my experience. If you have no assigned Sorting and Grouping,
changing the Order By clause in the RecordSource does change the displayed
sort order.
Click to expand...

Perhaps for simple reports, but in general you cannot assume that's going to
be what happen.
 
Ok, not trying to argue, just trying to make sure of the actual behavior. I
cannot reproduce this.

Most of my complex reports have sorting and grouping. But if I completely
remove ALL sorting and grouping in the report, and then change the
RecordSource to have a variety of Order By criteria, the report always sorts
according to the Order By. These reports may have 5 or 6 tables joined with
Where clauses. Perhaps not complex enough...

Do you have an example of a RecordSource SQL for which the report with no
grouping fails to sort on the SQL Order By?

- Stephen
 
Perhaps it has more to do with the default sort order on the table itself ?
But agree that a rule needs to be determined.
 
In my case, I have explicitly tried many different Order By clauses, on
multi-table joins, with sorts on different fields in different combinations,
and in all cases the Order By seems to carry forward into the report
display.

So it has nothing to do with the underlying table sort order, which Access
displays in key-field order unless you explicitly sort by something else.
And the SQL has multiple tables anyway.

I am using Access 2003, but I recall Access 2000 being the same way. But
obviously I have not tested all possibilities!

- Stephen
 
Back
Top