Report footer conflicting with query

[Guest]

I have a report that has been working fine. VBA code sets the RecordSource
conditionally as follows:

Me.RecordSource = _
SELECT [tbl_Accounts].* FROM [tbl_Accounts] ORDER BY [AccountNo]
(if user chooses to sort by Account number)

Me.RecordSource = _
SELECT [tbl_Accounts].* FROM [tbl_Accounts] ORDER BY [Lastname]
(if user chooses to sort by Last name)

Now I have added a Report Footer which has one control in it whose Control
Source is:
=Count([AccountNo])

As soon as I add this Report Footer, the "ORDER BY ..." is being ignored in
the report RecordSource. If I remove the Report Footer, then the
RecordSource ordering works fine again.

(The AccountNo field is a table key field.)

Any ideas why this is happening?

ctdak

 

[Duane Hookom]

I would never rely on the Order By property. If possible, use the Sorting
and Grouping dialog. You can set different values for these levels at
run-time.

 

[Allen Browne]

You cannot sort a report reliably based on the order specified in its
RecordSource.

Instead, use the Sorting'n'Grouping box to specify the field to sort by. You
can programmatically select the field to sort by in the Open event of the
report by setting the ControlSource of the GroupLevel. Details in article:
Sorting Records in a Report at run-time
at:
http://members.iinet.net.au/~allenbrowne/ser-33.html

If a report has nothing in its Sorting'n'Grouping box, you can use its
OrderBy property, remembering to set OrderByOn as well.

 

[Guest]

Advise taken. It works now.
Thanks.
ctdak

You cannot sort a report reliably based on the order specified in its
RecordSource.

Instead, use the Sorting'n'Grouping box to specify the field to sort by. You
can programmatically select the field to sort by in the Open event of the
report by setting the ControlSource of the GroupLevel. Details in article:
Sorting Records in a Report at run-time
at:
http://members.iinet.net.au/~allenbrowne/ser-33.html

If a report has nothing in its Sorting'n'Grouping box, you can use its
OrderBy property, remembering to set OrderByOn as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report that has been working fine. VBA code sets the RecordSource
conditionally as follows:

Me.RecordSource = _
SELECT [tbl_Accounts].* FROM [tbl_Accounts] ORDER BY [AccountNo]
(if user chooses to sort by Account number)

Me.RecordSource = _
SELECT [tbl_Accounts].* FROM [tbl_Accounts] ORDER BY [Lastname]
(if user chooses to sort by Last name)

Now I have added a Report Footer which has one control in it whose Control
Source is:
=Count([AccountNo])

As soon as I add this Report Footer, the "ORDER BY ..." is being ignored
in
the report RecordSource. If I remove the Report Footer, then the
RecordSource ordering works fine again.

(The AccountNo field is a table key field.)

Any ideas why this is happening?

ctdak