[RESOLVED] Choose which field to group by - Access

[SallyS]

Hi. I created a report in Access. I want to give the user a choice which fields she wants the report to be grouped by. Is there a way to do that in Access?

[Hack]

Are you creating your report based on an SQL query and just need to add an appropriate ORDER BY clause?

[SallyS]

yes, I created it using a query. In the sql I should add an order by saying order by [choose field] - how would that work?

[Hack]

Place all of the potential fields in a Multiselect ListBox. Have the users select whatever they want, then store they selections in a string. Put a button on by the Listbox that they need to click when they are done making their decision. Then, just loop through the listbox and there is your ORDER BY clause.

Private Sub cmdDone_Click()
Dim i As Long
Dim strOrderBy As String
    For i = 0 To List1.ListCount - 1
        If List1.Selected(i) Then
            strOrderBy = strOrderBy & List1.List(i) & ", "
        End If
    Next
    'this is necessary so that there is no comma after the last selection.
    strOrderBy = Left(strOrderBy, (Len(strOrderBy) - 2))
MsgBox "ORDER BY " & strOrderBy
End Sub

[SallyS]

I put a list box on the page - and wrote the sub in the button on click event but it's giving me the error that the method 'list' is not a valid method for a list box

[SallyS]

I used the ItemData method instead of the list method. Thanks for all your help!