Programaticaly set the sort order of a report 1

[McLiguori]

I have 2 reports. Same report, same query data source. One report I need ordered by last name, the other I need ordered by date.

The reports are called up with a button. The button opens a form into which the user enters a number of parameters. The base query gets these parameters (i.e. State, Zip Code, etc.) from the query and filters the proper records.

I know that the user can open the report in design view, go to sorting and grouping and set the sort order there. But, I need something simpler for the user. Preferably being able to select a sort order from a drop down list.

Is there a way to do this or do I have to create either a separate report or query for each sort order that I need?

Thanks in advance for your help

 

[Spenney]

This an example of something I did using an option group to allow the user to select their own sort order...

It does what you describe the user doing in your post, but their only involvement is selecting the option and clicking 'OK'

Private Sub cmdPreview_IR_Under_Review_Click()
    Dim strDocName As String
    Dim strOrder As String
    Dim rpt As Report
    strOrder = ""
        
    Select Case Me.grpSort_Order
    Case 1 'Applicant
        strOrder = "[app_name]"
    Case 2 'Sponsor
        strOrder = "[sponsor_name]"
    Case 3 'IR Ref
        strOrder = "[ir_ref]"
    Case 4 'Date Input
        strOrder = "[app_date_input]"
    End Select
    
    'Open report in design view, set the sort order, then open a preview
    strDocName = "rptIR_Under_Review"
    DoCmd.OpenReport strDocName, acViewDesign
    Set rpt = Reports(strDocName)
    rpt.OrderByOn = True
    rpt.OrderBy = strOrder
    DoCmd.Close acReport, strDocName, acSaveYes
    DoCmd.OpenReport strDocName, acViewPreview
    
End Sub

Any help?

 

[dhookom]

I prefer to not use the OrderBy property since it is always secondary to the Sorting and Grouping levels. You can set the sorting and grouping level control sources at runtime. There is sample code at

http://www.allenbrowne.com/ser-33.html.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[McLiguori]

Hi Spenny,

Thanks so much. After slight modifications, it worked like a charm.

Dhookom....the article was very helpful.

Thanks again,
McLigs

 

[dhookom]

Keep in mind that if there are any levels set in the Sorting and Grouping dialog, they will over-ride the OrderBy property.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]