Date Filter

[Jenova]

I’m having difficulty trying to filter my database for a certain date. In my application, I am attempting to retrieve records whose date corresponds to the date selected on my application. The results should then be displayed in a listview. If the selected date can not be found in the table then an error should show. So far, I have attempted to create a Do While loop to loop through the table, but this has failed.

Can anybody help me at all?

[Hack]

What database?

What are you currently doing that isn't working?

[LaVolpe]

Along with Hack's questions, why would you loop thru the table. Use a query to return the filtered recordset, then loop thru just those records with the only purpose of adding them to your listview.

[Jenova]

I have managed to come up with a solution, however, if the date does not exist in the table, my application will not alert the user. Essentialy, i was trying to do this.

Private Sub Command1_Click()
    Set adoRecordSet = New ADODB.Recordset
    
    ListView1.ListItems.Clear
    
    With adoRecordSet
        .Open "Table1", adoConnection, adOpenDynamic, adLockPessimistic, adCmdTable
        .MoveFirst
        
        Do While Not .EOF
            If .Fields("Date") = Date Then
                Set lvwListViewItem = ListView1.ListItems.Add(, , .Fields("Index"))
                With lvwListViewItem
                    .SubItems(1) = adoRecordSet.Fields("Date")
                    .SubItems(2) = adoRecordSet.Fields("Time")
                    .SubItems(3) = adoRecordSet.Fields("Person")
                    .SubItems(4) = adoRecordSet.Fields("Comment")
                End With
                .MoveNext
            Else
                .MoveNext
            End If
        Loop
    End With
End Sub

I have managed to resolve my own problem, although, if anyone can provide a more simple / efficient way to achieve this, i'd appreciate it.

[LaVolpe]

The more simple way is to not return the entire table, but use a query to return the filtered results. If the query returns no records, alert user. If it does return records, you would use basically the same loop to add the records to your listivew; you just won't have to validate the date field because the query already did that.

Now you may ask, so what's the difference? The difference is immense when you are talking about databases that contain hundreds, thousands records. Let's say only 5 match the date. Your method would require touching each record to find those 5, the query method only touches those 5.

Now regarding alerting the user in your current code. Simply check the ListView listitem count at the end. If it is zero, no records found & popup a msgbox as needed.

[Jenova]

The more simple way is to not return the entire table, but use a query to return the filtered results. If the query returns no records, alert user. If it does return records, you would use basically the same loop to add the records to your listivew; you just won't have to validate the date field because the query already did that.

Now you may ask, so what's the difference? The difference is immense when you are talking about databases that contain hundreds, thousands records. Let's say only 5 match the date. Your method would require touching each record to find those 5, the query method only touches those 5.

Now regarding alerting the user in your current code. Simply check the ListView listitem count at the end. If it is zero, no records found & popup a msgbox as needed.

Thanks! That certainly sounds like a more efficient way of doing things, however, i'm not sure how to set up a query using Visual Basic, i'm not acustom to ADO, this is the second time i have used it. If you could provide an example or any websites that have tutorials, i'd be very greatful.

[LaVolpe]

Ah, initiative -- love it. This website has some good stuff. Go to the main page, look below the VB6 forum, you will see databases. Open it & at the top of it is a link to the FAQs. I think you will find it very educational. Additoinally, the database forum may prove useful too when wanting to post db specific questions.

P.S. The query is not that difficult and the FAQ has some SQL sublinks also.