[RESOLVED] how to sort dates in listview with DTPicker

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

Code:

---

  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

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

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

Code:

---

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

---

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

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

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

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

Quote:

---

Originally Posted by Zvoni

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

---

exactly!!!!!!

try this

Code:

---

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

---

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:)

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:

Code:

---


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

---

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"

Try this

Code:

---

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.

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)

I have added this comment in the code to warn you

Code:

---

    ' 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.

tnk you its working now