Programmatically switch group levels in report

[Guest]

I'm trying to switch two group levels, ClassName and ItemDate, using VBA.
ClassName is grouplevel(0) and ItemDate is grouplevel(1). I want the user to
have the option to change ItemDate to grouplevel(0) and ClassName to
grouplevel(1). I tried changing the control source of each grouplevel to the
other group level, but because ClassName is text and ItemDate is Date/Time, I
receive an error whenever I switch to layout or print preview mode. I tried
using the GroupOn command to switch the field types, but because the GroupOn
command automatically assumes the field type, it doesn't work.

What is the most efficient way to switch the group levels in VBA?

 

[Guest]

You might want to check out the code at Allen Browne's page
http://www.allenbrowne.com/ser-33.html.

 

[Guest]

Yes, that helps somewhat, but it still doesn't address the issue that comes
up when the group levels are not the same data type. When I put in this code:

Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0).ControlSource = "ItemDate"
Me.GroupLevel(1).ControlSource = "ClassName"

End Sub

I receive an error message: "Data type mismatch in critera expression"

How do I change the data type for the group levels from date to text and
vice versa?

Thanks!

 

[Guest]

Just use text. Try change your date to a string with something like:
Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0).ControlSource = "=Format(ItemDate,'yyyymmdd')"
Me.GroupLevel(1).ControlSource = "ClassName"
End Sub
This keeps everything as a string. Make sure your design view has similar
expressions.