Sort report with VBA

[The Bey]

I have a report which is based on a query and the query is based on form data.

All is grand, but I would like to be able to sort the report differently depending on which button is clicked.

Would this be easier to do in VBA or should I just make a couple reports and program it so that a different report will be opened depending on which button is clicked... at the end of the day, the end user isn't going to see but I would like to know which is more convenient

 

[pbaldy]

Here's one way:

Private Sub Report_Open(Cancel As Integer)
  Select Case Forms!frmReports.fraGrouping
    Case 1 'sort/group on car type
      Me.GroupLevel(0).ControlSource = "CarType"
      Me.txtGroup.ControlSource = "CarDesc"
    Case 2 'sort/group on company
      Me.GroupLevel(0).ControlSource = "Company"
      Me.txtGroup.ControlSource = "Company"
    Case 3 'sort on date/time, no grouping
      Me.GroupLevel(0).ControlSource = "DispDateTime"
      Me.txtGroup.ControlSource = "DispDateTime"
      Me.GroupHeader0.Visible = False
  End Select
End Sub

 

[The Bey]

Will this method sort them ascending or descending? Or do I also need to specify that?

I've not used case-select before so a little run through would be appreciated. I'm gonna have a look at the help function to see if it can help me.

Also, I noticed that this is in the report_open event. How can I assign it so that on a button click, from a form, it will declare which case to use?

 

[The Bey]

I've attached a sample of my DB with a few records.

If you could, could you look at it and give me a hand on applying the case-select statement to my DB?

What I'm interested in doing is: Out of 4 categories, I'd like to be able to click a button from said category and that will load my report sorted with the soonest date to today, for that category, at the top