Sorting Functionality for Users

[Amanda]

I've already done this... but think I did it the hard way. I'm asking
for the best way to do this.

I have one table with data that's viewed in a listbox. (The table
contains inventory items and related facts.) The list box can be sorted
over 25 different ways. (For example, the list can be sorted by
quanity, ascending and descending. Also, you can enter a date range and
view records only between that range, and then set sort by quanity,
ascending or descending.)

I obviously have the option of creating 25 different queries, one for
each possible configuration: if the user checks this box and this box,
then a macro sets the RowSource for the listbox equal to a particular
query based on those check boxes. But, that requires making 25
different queries. I'm only dealing with one table... just trying to
sort it various ways and also refine it (limit it to particular
records).

Any suggestions for the best way of doing this? After the records are
sorted/limited as needed for the user in the listbox, it then needs to
be able to be exported to Excel or viewed in an Access report.

Thank you! I hope my question wasn't too vague...

 

[Brendan Reynolds]

Are you familiar with subforms? You get a lot of sorting and filtering
functionality for free when you use a subform.

 

[Pieter Wijnen]

Or you can use VBA code to generate the SQL & use that as the RowSource for
the ListBox

Private Sub FilterListBox() ' Called from every after_update event of the
filtering / sorting options
Const BaseSQL = "SELECT A.Field1, A.Field2,..,A.Fieldn FROM MyTable"
Dim WhereSQL As Variant, OrderSQL As Variant

'...............
If Me.SortByField3.Value = True Then
OrderSQL = (OrderSQL + ",") & "A.Field3 ASC"
End If
'.................

If Not IsNull(Me.StartField4.Value) And Not IsNull(Me.StartField.Value) Then
' DateField - As they are the most tricky
WhereSQL = (WhereSQL + " AND ") & "Field2 BETWEEN #" & _
Format(Me.StartField4.Value,"mm\/dd\/yyyy") & "# AND
#" & Format(Me.EndField4.Value,"mm\/dd\/yyyy") & "#"
ElseIf Not IsNull(Me.StartField4.Value)
WhereSQL = (WhereSQL + " AND ") & "Field2 >= #" & _
Format(Me.StartField4.Value,"mm\/dd\/yyyy") & "#"
ElseIf Not IsNull(Me.EndField4.Value)
WhereSQL = (WhereSQL + " AND ") & "Field2<= #" & _
Format(Me.EndField4.Value,"mm\/dd\/yyyy") & "#"
End If
'.......................
' Finaly
Me.TheListBox.Rowsource = BaseSQL & IIf(Not IsNull(WhereSQL), & " WHERE " &
WhereSQL,"") & _
IIf(Not IsNull(OrderSQL), & "
Order By " & OrderSQL,"")

Me.TheListBox.Requery
End Sub

This can be made as simple or complex as you like
But hopefully will give you a hint of one way to accomplish your goal

HTH

Pieter