[RESOLVED] how to sort dates in listview with DTPicker

[salsa31]

hey

i have a listview that shows logoff and log on for the user
with date
the listview show 12-6-2013 13-6-2013 and so on
how do i sort the date by using DTPicker ?
lets say a user loged in the system in 12-6-2013 and today is the 13-6-2013

al logs in the same date 12-6-2013 or 13-6-2013
i want to sort it with the DTPicker1

BTW this is the code that i use to load from the DB

  Set RS = CN.Execute("SELECT * FROM Logs ORDER BY LogId")
    While Not RS.EOF
        Set Itm = FrmSecurityreport.LsVw.ListItems.Add(, , RS!LogId, , "report")
        Itm.Tag = RS!LogId
        Itm.bold = True
        Itm.SubItems(1) = RS!LogDate
        Itm.SubItems(2) = RS!LogDay
        Itm.SubItems(3) = RS!LogName
        Itm.SubItems(4) = RS!LogIn
      
   If Not IsNull(RS!logout) Then
Itm.SubItems(5) = RS!logout
End If
  If Not IsNull(RS!LogTotal) Then
Itm.SubItems(6) = RS!LogTotal
End If
    
         
        
       Bar.Value = Bar.Value + 1
        RS.MoveNext
        DoEvents
    Wend
End Sub

tnx for the help

[4x2y]

What do you mean by "sort it with the DTPicker1"?

If you want to sort the listview itself, try this sample

Option Explicit

Private Sub Form_Load()
    Dim lvi As ListItem
    Dim j As Long
    
    ListView1.Sorted = True
    ListView1.View = lvwReport
    ListView1.FullRowSelect = True
    ListView1.ColumnHeaders.Add , , "", 2000
    ListView1.ColumnHeaders.Add , , "Log in", 2000
    ListView1.ColumnHeaders.Add , , "Log off", 2000
    For j = 1 To 30
        Set lvi = ListView1.ListItems.Add(, , Right$("00" & j, 2))
        lvi.ListSubItems.Add , , Format$(Now + j, "yyyy-mm-dd")
        lvi.ListSubItems.Add , , Format$(Now + j + 1, "yyyy-mm-dd")
    Next
End Sub

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)

    Dim j As Long
    
    If ListView1.Sorted = True Then

        ' Remove sort indicator.
        For j = 1 To ListView1.ColumnHeaders.Count
            If Left$(ListView1.ColumnHeaders(j).Text, 2) = "+ " Or Left$(ListView1.ColumnHeaders(j).Text, 2) = "- " Then
                ListView1.ColumnHeaders(j).Text = Mid$(ListView1.ColumnHeaders(j).Text, 2)
            End If
        Next
    
        ListView1.SortKey = ColumnHeader.Index - 1
    
        If ListView1.SortOrder = lvwAscending Then
            ListView1.SortOrder = lvwDescending
            ColumnHeader.Text = "- " & ColumnHeader.Text
        Else
            ListView1.SortOrder = lvwAscending
            ColumnHeader.Text = "+ " & ColumnHeader.Text
        End If
    
    
    End If

End Sub

[salsa31]

i mean sort the date login with dtpicker
the date in the listview

[4x2y]

Sorry, i don't understand, DTPicker shows only one date at a time

[salsa31]

each time i choose a date from datepicker it will show me from the listview the logs that i picked from the datepicker

[Zvoni]

He doesn't want to sort, but to filter!

[salsa31]

He doesn't want to sort, but to filter!

exactly!!!!!!

[4x2y]

try this

Option Explicit
Private Const mstrDateFormat As String = "dd-mm-yyyy"

Private Sub DTPicker1_CloseUp()
    Dim lvi As ListItem
    Dim j As Long
    Dim d As Date
    
    'Note: the date format must be exact like the format used in the listview.
    d = Format$(DTPicker1.Value, mstrDateFormat)
    
'    Search the listview for the selected date
    For j = 1 To ListView1.ListItems.Count
        Set lvi = ListView1.ListItems(j)
        If lvi.SubItems(2) = d Then ' replace 2 with the column's index you want to search.
            lvi.Selected = True
            lvi.EnsureVisible
'            Exit For ' uncomment this line if you want to select the first occurance only.
        End If
    Next

End Sub

Private Sub Form_Load()
    Dim lvi As ListItem
    Dim j As Long
    
    ListView1.Sorted = True
    ListView1.View = lvwReport
    ListView1.FullRowSelect = True
    ListView1.HideSelection = False
    ListView1.ColumnHeaders.Add , , "", 2000
    ListView1.ColumnHeaders.Add , , "Log in", 2000
    ListView1.ColumnHeaders.Add , , "Log off", 2000
    For j = 1 To 30
        Set lvi = ListView1.ListItems.Add(, , Right$("00" & j, 2))
        lvi.ListSubItems.Add , , Format$(Now + j, mstrDateFormat)
        lvi.ListSubItems.Add , , Format$(Now + j + 1, mstrDateFormat)
    Next
    

End Sub

[salsa31]

what is lvi stands for?

For j = 1 To 30>??

i dont understand the code here

please try to combine it with my code so i can understand

[4x2y]

The code under Form_Load is just for test, i don't have DB to load the dates from, so i fill the listview with some similar dates.

You only need to add the code under DTPicker1_CloseUp to your project, something like this:

Option Explicit
Private Const mstrDateFormat As String = "dd-mm-yyyy"

Private Sub DTPicker1_CloseUp()
    Dim lvi As ListItem
    Dim j As Long
    Dim d As Date
    
    'Note: the date format must be exact like the format used in the listview.
    d = Format$(DTPicker1.Value, mstrDateFormat)
    
'    Search the listview for the selected date
    For j = 1 To ListView1.ListItems.Count
        Set lvi = ListView1.ListItems(j)
        If lvi.SubItems(1) = d Then ' replace 2 with the column's index you want to search.
            lvi.Selected = True
            lvi.EnsureVisible
'            Exit For ' uncomment this line if you want to select the first occurance only.
        End If
    Next

End Sub

' MISSED SUB SIGNATURE
  Set RS = CN.Execute("SELECT * FROM Logs ORDER BY LogId")
    While Not RS.EOF
        Set Itm = FrmSecurityreport.LsVw.ListItems.Add(, , RS!LogId, , "report")
        Itm.Tag = RS!LogId
        Itm.bold = True
        Itm.SubItems(1) = Format$(RS!LogDate, mstrDateFormat) ' make sure the date format is similar to the one used in DTPicker1_CloseUp
        Itm.SubItems(2) = RS!LogDay
        Itm.SubItems(3) = RS!LogName
        Itm.SubItems(4) = RS!LogIn
      
   If Not IsNull(RS!logout) Then
Itm.SubItems(5) = RS!logout
End If
  If Not IsNull(RS!LogTotal) Then
Itm.SubItems(6) = RS!LogTotal
End If
    
         
        
       Bar.Value = Bar.Value + 1
        RS.MoveNext
        DoEvents
    Wend
End Sub

[salsa31]

tnk you its half working

i just need the other dates not to show up when i choose the date.

and if the date not found then msgbox " no such date"

[4x2y]

Try this

Private Sub DTPicker1_CloseUp()
    Dim lvi As ListItem
    Dim j As Long
    Dim d As Date
    
    'Note: the date format must be exact like the format used in the listview.
    d = Format$(DTPicker1.Value, mstrDateFormat)
    
    ' Call the sub that fill the listview from th db
    ' in order to remove the filter.
    '
    
    For j = ListView1.ListItems.Count To 1 Step -1
        Set lvi = ListView1.ListItems(j)
        If lvi.SubItems(2) <> d Then
            ListView1.ListItems.Remove (j)
        End If
    Next

End Sub

If the selected date is not exist, the listview will be empty, so there is no need for a msgbox.

If you have a lot of dates and it take time to read from the DB then you may need to cache the listview (in array or so) instead of reading from the DB after each filtering.

[salsa31]

works better just 1 thing

if i choose a date that is not on the listview it cleans the list view and thats ok
but if i choose after that a date that have records then its blank(the listview)

[4x2y]

I have added this comment in the code to warn you

    ' Call the sub that fill the listview from th db
    ' in order to remove the filter.

Filtering the listview means removing the items that we are not want to see, when changing the filter, we must fill the listview with unfiltered items then apply the new filter.

As i said, if you have a lot of dates and it take time to read from the DB then you may need to cache the listview (in array or so) instead of reading from the DB after each filtering.

[salsa31]

tnk you its working now