Problem with setting report sort order at run time

Juan Velasquez
Juan Velasquez used Ask the Experts™
on
Hello,
Below is the sql statement that is used at runtime.  I've placed this code in a query and it sorts as it is supposed to - in this case by Task Order.  Yet when I run the report, it is not being sorted by task order.  I then went ahead and created a query using the sql statement below and made it the recordsource of the report at design time.  I still had the same problem.  I've check for any groupings or sort orders that were set in the report at design time but couldn't find any.  This is an access 2007 database
SELECT qryProject.[TaskOrder], [OBS Master].OBS, [Lname] & ', '  & [Fname] AS Name, [OBS Master].Fname, [OBS Master].Lname, qryProject.[ChargeNumber], qryProject.Posted AS WeekEnding, qryProject.Workhours AS RegularHours, [FY11B]+[FY11OH] AS FY11Rate, [RegularHours]*[FY11Rate] AS RegularPay, qryProject.Overtime AS OvertimeHours, [OT11B]+[OT11OH] AS FY11otRate, [OvertimeHours]*[FY11otRate] AS OvertimePay, [OBS Master].ODC3 AS FY11odcRate, [OBS Master].Fee2 AS FY11FeeRate,[RegularPay]+[OvertimePay] AS TotalPay, [OBS Master].FY11B, [OBS Master].FY11OH, [OBS Master].OT11B, [OBS Master].OT11OH, project.Comments, project.locked, qryProject.source, [OBS Master].[Company ] FROM (Period INNER JOIN qryProject ON Period.[Week Ending] = qryProject.Posted) INNER JOIN [OBS Master] ON qryProject.OBS = [OBS Master].OBS WHERE qryProject.locked = False AND qryProject.source Like 'a' AND [Week Ending] = #4/22/2011# AND Company = 'EC' ORDER BY TaskOrder

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
open your report in design view
select sorting and grouping from the tool bars  [=
                                                               [=

select the field/s you want to do the sort on and select the sort orde
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Reports are intended to have sorting (and grouping) done by using the Grouping/Sorting dialog.  If you have *any* Grouping specified, Access completely ignores any sorting in the underlying query.  And it's a performance hit also, because when run, the query is still going to sort (= time), but be ignored by Access.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Oh ... and so, be sure to *remove* the sorting from your query.

mx
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
I want to be able to set the sort order at runtime via a report wizard that I created.  The final sql string that the wizard creates produces an sql string that when place in a query produces the correct sort.  However, when I set that sql string as the recordsource of the report, the records in the report are not sorted
Top Expert 2016

Commented:

<I want to be able to set the sort order at runtime via a report wizard that I created.> '

why do you want it this way?


Database Architect / Application Developer
Top Expert 2007
Commented:
"the records in the report are not sorted"
And the reason why is as I explained above.  If you want that to work (not recommended), then remove ANY Grouping/Sorting  from the Grouping Sorting dialog.

If you want to set the sorting at Runtime, then you will need to set the Report's OrderBy property via VBA code when the Report opens.

mx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial