Changing a grouping using code

[Funkyaccess]

Hi,

Is there anyway I can change my grouping on a specific field either by changing the field or changing the criteria eg a date field group by day/week/month/year

I have to date fields that I would like to use. If I can do this with code what is the best event to use to trigger this.

Or does it mean I should create another report with using the other date field.

 

[pbaldy]

I do this type of thing, referring back to the form used to open the report:

Private Sub Report_Open(Cancel As Integer)
  Select Case Forms!frmReports.fraGrouping
    Case 1 'group on car type
      Me.GroupLevel(0).ControlSource = "CarType"
      Me.txtGroup.ControlSource = "CarDesc"
    Case 2 '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

 

[Funkyaccess]

Thanks I'll give that a try. Does that cover grouping within a time frame i.e day/week/month/year or what ever you want a date field to be grouped by?

 

[pbaldy]

I think I misunderstood the goal. That changes which field a report groups on, but doesn't change the data itself. If you're saying that you might want the report where it groups/sums data by week one time, by month the next, that would be different. Is that what you meant? Like a user selectable grouping period?

 

[Funkyaccess]

Yes that correct. Though you first reply was helpful as I can now change the column that needs to be group by. I just need to change the period using a radio button option group.

 

[Funkyaccess]

anybody able to help?

 

[pbaldy]

Sorry, lost track of this thread. The only ways that come to mind would either involve changing the SQL of the underlying query, or perhaps having day/week/month/year fields in the query and grouping on the appropriate one. If you can post a sample db with enough test data to illustrate the problem, I'll try to play with it.

 

[Funkyaccess]

just looked at the Access help with GroupLevel and it mentioned other things of which GroupOn seems to be the one I'm after will let you know if I run into difficulties.