Query a date from database

[matrik02]

I am still finding the best solution.. I have join many forums but did not find a best place to answer the question. I attach a sample code


I have planting date, and I use DTpicker. I chose to plant the crop on 12/03/2007 (txtDate = Format(DTPicker1.Value, "dd/mm/yyyy")
And it list me the activities from the datasource (DBMS.MDB). You can see that, it list the activities with date in the listview before the planning date (12/03/2007 ). This activities, I should do before the planting date arrive.


From the list, you can see 26/01/2007 is the starting date for activities 1 and it took 7 days to finish this activities. The date expected to finish is on 1/02/2007

(26/01/2007,27/01/2007,28/01/2007,29/01/2007,30/01/2007,31/01/2007,1/02/2007)

The second activities start on 2/2/2007 and expected to finish on 4/2/2007 (2/02/2007,3/02/2007,4/2/2007) and it take 3 days to finish and so on...

From the list, I want to do a query:-

My Question is, I want to do a query to find what activities I should do for the spesific date base on the list.Let say, I query date 30/01/2007, Then It list the activities in a new listview. From the listview, It show me the result and it support in activities 1.

I have create a new dtpicker and the listview to show the result form the query.Have an idea?I have trouble to do a query by date.

I use ADODB connection and microsoft access

Code for listview


Public Sub AddLvItem()
On Error Resume Next
Dim newDate As Date
rec.MoveFirst
Do Until rec.EOF
If rec("hari") = "" Then
Set lvItem = lvJadual.ListItems.Add(, , "")
lvItem.ListSubItems.Add , , "" & rec("peruntukan")
lvItem.ListSubItems.Add , , "" & rec("aktiviti")
lvItem.ListSubItems.Add , , "" & rec("sasaran")
lvItem.ListSubItems.Add , , "" & rec("jenisinput")
lvItem.ListSubItems.Add , , "" & rec("tindakan")
'lvItem.ListSubItems.Add , , "" & rec("hari")

'Isnull(rec("hari")) ialah test untuk tengo kalau nilai yang dibaca dpd field hari ialah null atau kosong
ElseIf IsNull(rec("hari")) Then
Set lvItem = lvJadual.ListItems.Add(, , "")
lvItem.ListSubItems.Add , , "" & rec("peruntukan")
lvItem.ListSubItems.Add , , "" & rec("aktiviti")
lvItem.ListSubItems.Add , , "" & rec("sasaran")
lvItem.ListSubItems.Add , , "" & rec("jenisinput")
lvItem.ListSubItems.Add , , "" & rec("tindakan")
'lvItem.ListSubItems.Add , , "" & rec("hari")
Else
newDate = DateAdd("d", rec("hari"), DTPicker1)
Set lvItem = lvJadual.ListItems.Add(, , "" & Format(newDate, "dd/mm/yyyy"))
lvItem.ListSubItems.Add , , "" & rec("peruntukan")
lvItem.ListSubItems.Add , , "" & rec("aktiviti")
lvItem.ListSubItems.Add , , "" & rec("sasaran")
lvItem.ListSubItems.Add , , "" & rec("jenisinput")
lvItem.ListSubItems.Add , , "" & rec("tindakan")
'lvItem.ListSubItems.Add , , "" & rec("hari")
rec("tarikh") = Format(newDate, "dd/mm/yyyy")
rec.Update
End If
rec.MoveNext
LoopAttached Images