How can I sort a listbox by col. 1, then by col. 2?

[wardw]

I have a listbox named lstSearch in a form, with four columns populated from a database. When the form opens, the records are sorted A to Z by the first column (last names). There are over 6400 records, though, so there are lots of Smiths, Joneses, and so on, and the first names of these common last names appear in random order, like this:

Smith | Terry
Smith | Allen
Smith | Denise
Smith | Bob

I'd like the listbox to be sorted first by the last name column, then by the first name column, like this:

Smith | Allen
Smith | Bob
Smith | Denise
Smith | Terry

Can this be done by altering the SQL query that populates the list, or by adding some VBA code? The current row source for the listbox is:

Private Function basOrderby(col As String, xorder As String) As Integer
Dim strSQL As String
    
    strSQL = "SELECT DISTINCTROW PersonID, LastName, FirstName, MiddleName, DeathDate "
    strSQL = strSQL & "FROM AllDeathRecords "
    strSQL = strSQL & "ORDER BY " & col & " " & xorder
    Me!lstSearch.RowSource = strSQL

End Function

I don't understand the ORDER BY portion too well (I didn't write the function myself), but I tried to add the second column to the sort criteria by using

strSQL = strSQL & "ORDER BY " & col, col & " " & xorder

but VBA didn't like the syntax (can't blame it).



The app itself is more complex than I've described: Each of the columns has a button for sorting the records A-Z, then back to Z-A, but I don't think that affects sort order of the listbox when it first appears.

Any ideas on doing an ORDER BY by multiple columns?

[June7]

col is a variable that is set when the function is called. If you want to apply a second sort criteria then need another variable.

Private Function basOrderby(col1 As String, xorder1 As String, col2 As String, xorder2 As String) As Integer
...
strSQL = strSQL & "ORDER BY " & col1 & " " & xorder1 & ", " & col2 & " " & xorder2
...
End Function

Then call the function

basOrderBy(field1, {"Ascen", "Desc"}, field2, {"Ascen", "Desc"})

[ssanfu]

You can try this: if the first argument (col) is "LastName", then also sort by firstname

Private Function basOrderby(col As String, xorder As String) As Integer
   Dim strSQL As String

   strSQL = "SELECT DISTINCTROW PersonID, LastName, FirstName, MiddleName, DeathDate "
   strSQL = strSQL & "FROM AllDeathRecords "

   If col = "LastName" Then
      strSQL = strSQL & "ORDER BY " & col & " " & xorder & ",  FirstName"
   Else
      strSQL = strSQL & "ORDER BY " & col & " " & xorder
   End If


   Me!lstSearch.RowSource = strSQL

End Function

This version of the code adds an optional argument "col2":

Private Function basOrderby(col As String, xorder As String, Optional col2 As String) As Integer
   Dim strSQL As String

   strSQL = "SELECT DISTINCTROW PersonID, LastName, FirstName, MiddleName, DeathDate "
   strSQL = strSQL & "FROM AllDeathRecords "

   If IsMissing(col2) Then
      strSQL = strSQL & "ORDER BY " & col & " " & xorder
   Else
      strSQL = strSQL & "ORDER BY " & col & " " & xorder & ",  " & col2
   End If


   Me!lstSearch.RowSource = strSQL

End Function

"col2" will always sort ascending

The 2nd example is more flexible. You would call it

basOrderby("LastName", "Desc", "FirstName")

or

basOrderby("DeathDate", "Desc")


These are untested, but should work...

[wardw]

Thanks, ssanfu; I tried your first set of code lines, and it worked after I clicked any of the command buttons for re-sorting. The LastName column, and then the FirstName column, were sorted the way I wanted them.

But for some reason, when the form first appears the columns are still sorted the old way: LastName is sorted A-Z and FirstName is random. The Form's On Load event is currently [Event Procedure], and the list box (1stSearch) has [Event Procedure] for its After Update and On Dbl Click events. (Double-clicking a record in the list box opens the full record in another form.)

Currently, the first field in the SQL query, PersonID, is the primary key field of the AllDeathRecords table, but I've concealed that so it doesn't appear in the listbox. For some reason, the "col" variable in my original code seems to be correctly assigned to the LastName field, fortunately. That may be because it's the "first column" visible in the listbox. So in your code, col = "LastName" would be true, and the two-column sort should happen correctly.

It looks as if either your code isn't being carried out soon enough, like at form load, or col = "LastName" is being interpreted as False. I tried col = "PersonID" instead, but it didn't change the form's initial sort order.

[wardw]

Thanks, June7, for your quick reply. I tried your code, but VB objected to the syntax of the function call. When I open the 1stSearch form, an error message says "Compile error: Syntax error", and VB opens showing the Private Sub Form_Load() line highlighted in yellow, and its

basOrderby(LastName, {"Ascen", "Desc"}, FirstName, {"Ascen", "Desc"})

line in red. When I tried to edit that line, another error appeared: "Compile error: Invalid character", and the brace before the first "Ascen" was highlighted.

Did I put that function call line in the wrong place?

[June7]

You have to pick either "Ascen" or "Desc" for the argument value. The braces mean what is enclosed are values you must choose from. Choose one and type it within quote marks because it is a text string but without the braces.

[ssanfu]

when the form first appears the columns are still sorted the old way: LastName is sorted A-Z and FirstName is random.

What is the default row source for the list box??
If you want the form to open and the list box to be sorted LastName, FirstName, then set the row source of the list box that way.

[amrut]

As asked by Steve, have you tried the below as the default row source of the listbox ?

SELECT DISTINCTROW AllDeathRecords.PersonID, AllDeathRecords.LastName, AllDeathRecords.FirstName, AllDeathRecords.MiddleName,
AllDeathRecords.DeathDate FROM AllDeathRecords ORDER BY AllDeathRecords.LastName, AllDeathRecords.FirstName, AllDeathRecords.DeathDate;

[wardw]

Wow, such a simple solution; all I had to do was add FirstName and DeathDate to the ORDER BY and the initial sort works perfectly. Thanks so much, amrut and Steve, for your quick help.