User decides how to sort

[NISMOJim]

I'm working on a form to enter search parameters to generate a Parts report. My report has fields like Arrival date, Disposal date, Part Number, Description, etc. Users want to be able to sort the report by either Arrival Date, or Disposal Date, depending on what they are working on. I would like to add these options to my form, then the user would click a command button to open the report based on their criteria. My skills are below par, so hopefully something fairly easy is possible. Any suggestions?



As always, thank you for your guidance!

[June7]

Use VBA to construct sort criteria and apply to form OrderBy property.

[NISMOJim]

But the only thing on the parameters form is a combobox to select the name of the part owner, and an option group to sort by either Arrival Date or Disposal Date. The relevant data is displayed in the report. The name filtering (based on the name selected on the parameters form) is done in the underlying query when the report opens, I just can't grasp how to sort by either date field based on the selection. I was thinking something like...If opt1 = 1 then sort by Arrival Date, descending. If opt2 = 2 then sort by Disposal Date, descending. Maybe this would be somewhere on the OnOpen property of the report or an If statement in the sort property of the query? Just not sure how to make it happen.

[June7]

Perhaps code in the Option Group frame control Click or AfterUpdate event.

Me.OrderBy = IIf(Me.grpDate = 1, "[Arrival Date]", "[Disposal Date]") & " DESC"

Access tends to save the last Filter and OrderBy parameters when the form closes. That's why I set the FilterOnLoad property to No.

I've never coded for dynamic sorting.

[NISMOJim]

I have to (again) apologize for my ignorance June7, I'm just not getting it. I bounced around the internet for most of today, and came up with this...

Here is the code on the "Open Report" command button on the parameters form (part owner's name selected as a filter built into the query, and a sorting selection made by the user via an options group to either sort by Arrival Date or Disposal Date)...

Private Sub cmdReport_Click()
Dim strDoc As String
Dim strSOrder As String
strDoc = "rptDisposition"

If Me.grpSort = 1 Then
strSOrder = "OrderBy [DateRecd] DESC"
ElseIf Me.grpSort = 2 Then
strSOrder = "OrderBy [DispDate] DESC"
End If

DoCmd.OpenReport strDoc, acViewPreview, , , , strSOrder

End Sub

Then on the "On Open" event for the report is this...

Private Sub Report_Open(Cancel As Integer)
Dim strSOrder As String
strSOrder = Me.OpenArgs


End Sub

I'm not getting any errors, but I'm also not getting any change in the way the report records are sorted. Is the problem in the syntax of the "OrderBy" part, or somewhere (everywhere) else?

[June7]

Don't include OrderBy in the string.

DoCmd.OpenReport strDoc, acViewPreview, , , , IIf(Me.grpSort = 1, "[Arrival Date]", "[Disposal Date]") & " DESC"

Then:

Me.OrderBy = Me.OpenArgs

Then also need:

Me.OrderByOn = True

Won't work on report set up with Grouping & Sorting.

[NISMOJim]

That did the trick! Thank you again for your help. People like you make people here think I know what I'm doing.

[June7]

Might find this of interest http://allenbrowne.com/ser-33.html