best course of action with sorting

[icemonster]

i have this code to populate a listbox, it can search names, dates and status etc. but i only have 1 option for sorting, what's the best possible sol'n in regards to when a user clicks say the label for client, it will sort it out a-z or z-a?

Function SetScheduleList()
'set starting sql statement
        strStartSql = "SELECT qryScheduleMASTERLIST.SCHEDULEID, qryScheduleMASTERLIST.SCHEDCLIENT, " _
                    & "qryScheduleMASTERLIST.SCHEDEMPLOYEE, qryScheduleMASTERLIST.WORKDATEABBR, qryScheduleMASTERLIST.SCHEDWORKDATE, " _
                    & "qryScheduleMASTERLIST.ServiceType, qryScheduleMASTERLIST.RateType, qryScheduleMASTERLIST.SCHEDSTARTHR, " _
                    & "qryScheduleMASTERLIST.SCHEDENDHR, qryScheduleMASTERLIST.SCHEDSTATUSABBR, qryScheduleMASTERLIST.SCHEDVERIFIED, " _
                    & "qryScheduleMASTERLIST.COMMENT, qryScheduleMASTERLIST.SCHEDCLIENTID, qryScheduleMASTERLIST.WORKEDDAY, " _
                    & "qryScheduleMASTERLIST.SCHEDEMPLOYEEID, qryScheduleMASTERLIST.SCHEDSERVICETYPE, qryScheduleMASTERLIST.SCHEDCAREGIVERTYPE, " _
                    & "qryScheduleMASTERLIST.SCHEDRATE, qryScheduleMASTERLIST.SCHEDSTATUS, " _
                    & "qryScheduleMASTERLIST.SCHEDCOMMENT FROM qryScheduleMASTERLIST "

If Me.cboStatus > 0 Then
    lngScheduleStatusID = Me.cboStatus
    strWhereSql = "Where qryScheduleMASTERLIST.SCHEDSTATUS = " & lngScheduleStatusID & " "
    Else
        strWhereSql = ""
End If

If Me.cboClient > 0 Then
    lngClientID = Me.cboClient
    'determine if there is any existing value in the "strwheresql" variable
    If strWhereSql = "" Then
        strWhereSql = "Where qryScheduleMASTERLIST.SCHEDCLIENTID = " & lngClientID & " "
    Else
        strWhereSql = strWhereSql & "And qryScheduleMASTERLIST.SCHEDCLIENTID = " & lngClientID & " "
    End If
End If

If Me.cboEmployee > 0 Then
    lngClientID = Me.cboEmployee
    'determine if there is any existing value in the "strwheresql" variable
    If strWhereSql = "" Then
        strWhereSql = "Where qryScheduleMASTERLIST.SCHEDEMPLOYEEID = " & lngClientID & " "
    Else
        strWhereSql = strWhereSql & "And qryScheduleMASTERLIST.SCHEDEMPLOYEEID = " & lngClientID & " "
    End If
End If

'check for values entered for start and ending date parameters
If Not IsNull(Me.txtStartDate) And Not IsNull(Me.txtEndDate) Then
    'read the dates selected in the variables
    dtStartDate = Me.txtStartDate
    dtEndDate = Me.txtEndDate
    If strWhereSql = "" Then
        strWhereSql = "WHERE qryScheduleMASTERLIST.SCHEDWORKDATE Between #" & dtStartDate & "# " _
                    & "And #" & dtEndDate & "# "
    Else
        strWhereSql = strWhereSql & "AND qryScheduleMASTERLIST.SCHEDWORKDATE Between #" & dtStartDate & "# " _
                    & "And #" & dtEndDate & "# "
    End If
End If

If Me.grpStatus <> 3 Then
    If Me.grpStatus = 1 Then
        bolStatus = True
    Else
        bolStatus = False
    End If
    If strWhereSql = "" Then
        strWhereSql = "WHERE qryScheduleMASTERLIST.SCHEDVERIFIED=" & bolStatus & " "
    Else
        strWhereSql = strWhereSql & "AND qryScheduleMASTERLIST.SCHEDVERIFIED=" & bolStatus & " "
    End If
End If

strSortOrderSql = " ORDER BY qryScheduleMASTERLIST.SCHEDWORKDATE;"

strSQL = strStartSql & strWhereSql & strSortOrderSql
With Me.lstSchedule
    .RowSource = strSQL
    .Value = Null
    
End With
'Me.cmdEdit.Enabled = False
Me.lblListInfo.Caption = Me.lstSchedule.ListCount & " Schedule Records"
        
End Function

 

[John Big Booty]

Have you seen this thread that deals with sorting a listbox by clicking in the header row?

 

[icemonster]

yeah i have seen that, but does that apply to my current sql statement? can i use the code with the existing code that i have?

 

[icemonster]

hey, i tried using the code with my exsiting code but doesn't seem to work