hours display is incorrect

[kimangel]

hi experts, gud am!

I need your help on my DTR form. When I generate report for the total hours, the counts on the hours is correct only when time in and time out difference is more than 30 minutes but when it's less than 30 minutes for example: 12:46:13 t0 12:49:17 it displays incorrect hours and the hours on the other day is disregarded.

e.g.  3/1/13 - 8:00:00 am to 5:00:00 pm
      3/2/13 - 8:00:00 am to 5:00:00 pm
      3/3/13 - 8:00:00 am to 5:00:00 pm
      3/4/13 - 12:46:13 am to 12:49:17pm
      the results is 0.05 hours
      when 3/4/13 is excluded the result is 24 hours = correct

Pls help me check my code below. Thanks a lot.

Private Sub cmdSearch_Click()

Dim rs As ADODB.Recordset
Dim rsTotalHrs As ADODB.Recordset
Dim sTrID As String
Dim Total As Integer

Dim xhours As String
Dim Intime As String
Dim Outtime As String

Dim iHours As Integer
Dim iMins As Integer
Dim regHours As Integer
Dim i As Integer
Dim ctr As Integer
Dim dtFrom, dtTo As Date

dtFrom = DTPicker1.Value
dtTo = DTPicker2.Value
sTrID = txtSearch.Text


Set rs = New ADODB.Recordset
rs.Open "Select Count(DateIn) as Total from Time_In where Employees_IdNo = '" & sTrID & "'  AND DateIn BETWEEN #" & dtFrom & "# AND #" & dtTo & "#", cn, adOpenKeyset, adLockPessimistic

If Not rs.EOF Then
lblTotal_Days.Caption = rs!Total
lblNoOfDays.Caption = rs!Total


'START : Computation for HOURS and OVERTIME
Me.cmdSearch.Enabled = False
txtRegHolidays.SetFocus

ctr = lblTotal_Days.Caption
regHours = 8 * ctr
Set rsTotalHrs = New ADODB.Recordset
rsTotalHrs.Open "SELECT DateIn, TimeIn, TimeOut FROM Time_In WHERE Employees_IdNo = '" & sTrID & "' AND DateIn BETWEEN #" & dtFrom & "# AND #" & dtTo & "# ", cn, 1, 2

Ado.RecordSource = "Select Employees_IdNo, DateIn, TimeIn, TimeOut from Time_In where Employees_IdNo = '" & sTrID & "' AND DateIn >=#" & dtFrom & "# AND DateIn <= #" & dtTo & "# order by DateIn asc"
Ado.Refresh

For i = 1 To ctr Step 1
    Intime = TimeValue(rsTotalHrs!TimeIn)
    Outtime = TimeValue(rsTotalHrs!TimeOut)
    iMins = DateDiff("n", Intime, Outtime)

    iHours = iMins / 60
    iMins = iMins Mod 60

    If (iHours > 1) Then

        iHours = iHours - 1
        lblTotal_Hours.Caption = lblTotal_Hours + iHours

    ElseIf (iHours = 1) Then

        iHours = iHours
        lblTotal_Hours.Caption = lblTotal_Hours + iHours
    Else

        iHours = iMins / 60
        lblTotal_Hours.Caption = iMins / 60

    End If

    If ((lblTotal_Hours - regHours) < 1) Then
        lblOverTime.Caption = 0
    Else
        lblOverTime.Caption = lblTotal_Hours - regHours
        lblTotal_Hours = lblTotal_Hours - lblOverTime.Caption
    End If

    rsTotalHrs.MoveNext
Next

'END : Computation for HOURS and OVERTIME

rs.Close
Set rs = Nothing
Exit Sub
rs.Open "Select Sum([xhours])as totalxhours from Time_In where Employees_IdNo  = '" & sTrID & "'", cn, 2, 3

Ado.RecordSource = "Select Employees_IdNo, DateIn, TimeIn, TimeOut from Time_In where Employees_IdNo = '" & sTrID & "'"
Ado.Refresh
Ado.Refresh
Ado.Refresh

Intime = rs!TimeIn
Outtime = rs!TimeOut

xhours = Timediff(Intime, Outtime)

lblTotal_Hours.Caption = rs!totalxhours

rs.Close
Set rs = Nothing
Else
If rs.State = 1 Then rs.Close: Set rs = Nothing
MsgBox "No records found! ", vbExclamation, "DailyRecords"
End If

End Sub

Public Function Timediff(ByVal time1 As String, ByVal time2 As String) As String

Dim MinsDiff, TheHours As String

MinsDiff = DateDiff("n", time1, time2)

MinsDiff = IIf(MinsDiff < 0, MinsDiff + 1440, MinsDiff)
TheHours = Format(Int(MinsDiff / 60), "00")
MinsDiff = Format(MinsDiff Mod 60, "00")
Timediff = TheHours & ":" & MinsDiff
End Function

[kimangel]

Hi experts, badly need help on this. Thanks a lot.

[TnTinMN]

I am not going to try and figure out where your logic went wrong, but rather I am going to show you a different way.

Date values are store as Doubles that are referenced to a base value. These values can be either added to or subtracted from and the result converted back to a meaningful values.

Here is the basic logic:

    Dim d1 As Date
    ' assume mm/dd/yyyy date format
    d1 = CDate("1/1/2013 8:05 AM")

    Dim d2 As Date

    d2 = CDate("1/3/2013 6:00:30 PM")

    Dim diff As Double
    diff = d2 - d1

    ' get difference in days
    ' days is the interger part of the number
    Dim diffDays As Integer
    diffDays = Fix(diff)

    ' subtract off the days
    diff = diff - diffDays

    ' get the number of hours
    ' multiply remainder by 24

    diff = diff * 24#

    'the integer part is now the number of hours

    Dim diffHours As Integer
    diffHours = Fix(diff)

    ' subtract off the hours
    diff = diff - diffHours

    ' multiply by 60 to get minutes

    diff = diff * 60#
    Dim diffMinutes As Integer
    diffMinutes = Fix(diff)
    diff = diff - diffMinutes


    ' multiply by 60 to get seconds

    diff = diff * 60
    Dim diffSeconds As Integer
    diffSeconds = Fix(diff)

[kimangel]

thank you sir TnTinMN.

[Jx_Man]

thank you sir TnTinMN.

Then mark this thread as solved :)

[kimangel]

No sir, i'm not yet done with this. I still trying to figure out but as soon as im done expect a solve mark. Thanks