getting the range of date and getting sum of the fields from database

[kimangel]

Hi experts, I have a Daily Records form of an employee.

my fields are;

search box
search button
datefrom (datepicker)
dateto(datepicker)
datagridview (fields are Employees_IdNo, DateIn, TimeIn and TimeOut)
totaldays
totalhours

The scenario is this, first search for an employee using Employee number, set the datefrom and dateto to two datepickers and click Seach button. After clicking, on the datagridview the dates will display from the range set.
Second, when the range date already displayed on datagridview, the label -totaldayswork- automatically display its total days as well as the total number of hours work for regular and overtime. The time of overtime is after regular office hours eg. 9:00 am to 5:00pm- after 5:00pm is overtime.

I have below codes but I see no results;

Private Sub cmdSearch_Click()

Dim sTrID As String

sTrID = txtSearch.Text

Set rs = New ADODB.Recordset
rs.Open "Select Employees_IdNo,DateIn, TimeIn, TimeOut  from Time_In where Employees_IdNo = '" & sTrID & "'", cn, adOpenKeyset, adLockPessimistic

If Not rs.EOF Then

Ado.RecordSource = "Select Count(DateIn) as daycount from Time_In where Employees_IdNo = '" & sTrID & "'"
Ado.Refresh


lblTotal_Days.Caption = daycount

''no code yet for the totalhours and ovetime

rs.Close
Set rs = Nothing
Else
MsgBox "No records found! ", vbExclamation, "DailyRecords"
End If
End Sub

[kimangel]

Hi experts, I have below recoded from above to get the date range from database in datagrid view. I still encounter error. Please help me check. Tnx a lot.

Private Sub cmdSearch_Click()

Dim sTrID As String

sTrID = txtSearch.Text

Set rs = New ADODB.Recordset
rs.Open "Select Employees_IdNo,DateIn, TimeIn, TimeOut  from Time_In where Employees_IdNo = '" & sTrID & "'", cn, adOpenKeyset, adLockPessimistic

If Not rs.EOF Then

Ado.RecordSource = "Select Count(DateIn) as daycount from Time_In where Employees_IdNo = '" & sTrID & "'"
Ado.Refresh


lblTotal_Days.Caption = daycount

rs.Close
Set rs = Nothing
Else
MsgBox "No records found! ", vbExclamation, "DailyRecords"
End If
End Sub

Private Sub cmdOK_Click()

Dim dtFrom, dtTo As Date

dtFrom = DTPicker1.Value
dtTo = DTPicker2.Value

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Time_In WHERE Employees_IdNo = '" & txtSearch.Text & "' and DateIn BETWEEN #" & dtFrom & "# AND #" & dtTo & "' ORDER BY DateIn", cn, adOpenKeyset, adLockPessimistic

Ado.RecordSource = "Select * From Time_In WHERE Name = '" & txtSearch.Text & " ' and DateIn Between #" & dtFrom & "# AND #" & dtTo & "' Order by DateIn "
Ado.Refresh

If rs.RecordCount > 0 Then
End If

End Sub