Report footer conflicting with query

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

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
 
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.
 
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.
 
Advise taken. It works now.
Thanks.
ctdak


Allen Browne said:
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.

ctdak said:
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
Click to expand...
Click to expand...
 
Back
Top