Member Avatar for 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
  • 1 Contributor
  • 1 Reply
  • 211 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by kimangel

Dani AI

Generated

— short diagnosis and a working approach.

Your errors come from three places: (1) the SQL string concatenation has mismatched/extra quotes (the BETWEEN clause in your second snippet ends with an unmatched single quote), (2) you set Ado.RecordSource to queries that reference the wrong field/name and never read the Count value back from a recordset, and (3) building inline date literals is fragile in Access (date literals use # and are interpreted according to regional settings). Fix the SQL quoting or, better, use parameterized commands so the provider handles dates for you. (support.microsoft.com)

Use an ADODB.Command with parameters (safer, avoids formatting problems). Example pattern (VB6/VBA ADO):

Dim cmd As ADODB.Command, rs As ADODB.Recordset
Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = cn
  .CommandText = "SELECT Employees_IdNo, DateIn, TimeIn, TimeOut FROM Time_In WHERE Employees_IdNo = ? AND DateIn BETWEEN ? AND ? ORDER BY DateIn"
  .CommandType = adCmdText
  .Parameters.Append .CreateParameter("pID", adVarChar, adParamInput, 50, empID)
  .Parameters.Append .CreateParameter("pFrom", adDate, adParamInput, , dtFrom)
  .Parameters.Append .CreateParameter("pTo", adDate, adParamInput, , dtTo)
  Set rs = .Execute
End With

Using CreateParameter/Append avoids ambiguous date parsing and SQL-quote mistakes. (learn.microsoft.com)

Compute days and hours in code (handles duplicate rows per date and the regular/OT split). Walk the recordset, use DateValue/TimeValue, aggregate unique dates (Dictionary) for total days, sum (TimeOut - TimeIn)*24 for hours, and compute regular = overlap between [9:00,17:00] and the employee interval; overtime = time after 17:00. If you prefer SQL-only for unique-day counts, Access does not support COUNT(DISTINCT) — you must wrap a DISTINCT subquery or use DCount/QueryDef; otherwise count unique DateValue results in code. (accessjumpstart.com)

Quick checklist before rerunning:

  • Fix the stray/mismatched quotes in your SQL strings.
  • Use the same key field everywhere (Employees_IdNo, not Name).
  • If you keep inline SQL, wrap Access dates in # and Format them unambiguously or, better, use parameters.
  • Read aggregate fields from the recordset (e.g., rs!daycount) — don’t assume a variable appears magically.
  • Close and Set Nothing on Recordset/Command objects when done.

These changes will stop the syntax errors and give you a reliable place to compute total days, regular hours, and overtime.

Member Avatar for 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
Edited by kimangel
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.