I have one textbox(displays count result), one button(performs the counting), and two DateTimePickers(for date period from and to) in a form. I am using MS Access database for my program with two date fields(date1 and date2). What I want is to count how many records dated with(dates given from datetimepickerfrom and datetimepickerto). The counting is with condition like: If date2 is greater or later than date1 then the counting will base on date2, else on date1. Any suggestion or modification in my code is highly appreciated.


This is my code but it results error:"Item cannot be found in the collection corresponding to the requested name or ordinal."
countrec = New ADODB.Recordset
With countrec

If .Fields("Date2").Value > .Fields("Date1").Value Then
.Open("select count(*) as count2 from Docstable where Date2 >=#" & DateTimePickerfrom.Value.Date & "# and Date2 <=#" & DateTimePickerto.Value.Date & "#", countcon, 2, 3)
textbox1.Text = .Fields("count2").Value
.Close()
else
.Open("select count(*) as count1 from Docstable where Date1 >=#" & DateTimePickerfrom.Value.Date & "# and Date1 <=#" & DateTimePickerto.Value.Date & "#", countcon, 2, 3)
textbox1.Text = .Fields("count1").Value
.Close()

end if

End With