compare Time between 2 table

[gracehskuo]

hi,please help.how come compare time between 2 table from ms.access database.This 2 table contain employeeno,date and Time.when get the compare time data will save to the overtime table comparetime field.


this is the current code:

Private Sub Command2_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset


Set cn = New ADODB.Connection
cn.Open "Provider = 'Microsoft.Jet.OLEDB.4.0';" & "Data Source = " & App.Path & "\overtime.mdb;"
   

    Set rs = New ADODB.Recordset
    rs.Open "SELECT Time FROM Attendance ", cn, adOpenStatic, adLockOptimistic
      
      
  Set rs1 = New ADODB.Recordset
      rs1.Open "SELECT Time FROM Overtime", cn, adOpenStatic, adLockOptimistic
 

t1 = Format(rs!Time, "hh:mm:ss")
t2 = Format(rs1!Time, "hh:mm:ss")

t3 = DateDiff("n", t1, t2)
MsgBox " t3"

End Sub

please help.thanks.

[leinad31]

Above code will only work if there are no day spanning OT, e.g. no midnight shift OT. Try including date in DateDiff().

[gracehskuo]

leinad31:
hi,thank ya reply.the method i using is correct??how to use datediff()
within the time comparing??

i'm trying to do as this,but the query having problem.i'm not sure this method can work or not.

strsql = "UPDATE OverTime INNER JOIN Attendance " & _
         "ON OverTime.overtimedate = Attendance.overtimedate " & _
         "And OverTime.employeeno = Attendance.employeeno " & _
        "And datediff(n,Overtime.TimeFrom,Attendance.Time) & _
         "Set OverTime.match=1"

please guide.thanks.

[leinad31]

I suggest you shift Attendance criteria to a WHERE EXISTS implementation as there are complications to modifying views (result of INNER JOIN)... modifying one table is simpler.

[gracehskuo]

leinad31;
hi,please help.when i run the query have error appear.

this is my current code:

strsql = "UPDATE OverTime " & _
            "Set OverTime.match=1 " & _
            "FROM OverTime INNER JOIN Attendance " & _
            "ON OverTime.overtimedate = Attendance.overtimedate " & _
            "And OverTime.employeeno = Attendance.employeeno " & _
           "And datediff('n',Overtime.TimeFrom,Attendance.Time)=0"

[anhn]

Why use DateDiff()=0 to compare only minutes of 2 time values? Just simply check TimeA = TimeB.

Becareful when using Time as field name. Time is a built-in function that retrurns the system time, when using it as field name you should surround it with square brackets [Time].

Two ways to write the UPDATE query as below and noted that
-([OverTime].[TimeFrom]=[Attendance].[Time])
will returns 1 if [TimeFrom] = [Time], otherwise returns 0.

UPDATE OverTime INNER JOIN Attendance 
ON (OverTime.overtimedate = Attendance.overtimedate) 
AND (OverTime.employeeno = Attendance.employeeno) 
SET OverTime.[match] = -([OverTime].[TimeFrom]=[Attendance].[Time]);

UPDATE OverTime, Attendance 
SET OverTime.[match] = -([OverTime].[TimeFrom]=[Attendance].[Time]) 
WHERE (OverTime.employeeno=[Attendance].[employeeno]) 
AND (OverTime.overtimedate=[Attendance].[overtimedate]);

It also can be written with WHERE EXISTS as leinad mentioned.

[gracehskuo]

anhn:
hi,thanks ya reply.the statement is work.but then i want to get the datediff from overtime.Timefrom and attendance.Time.so the time different will update to the Match fields.i'm able to do the minus the time but the total hours have problem.

Overtime.Timefrom is 24 hours format
attendance.Time is 12 hours format

so when 19:00-6:30pm=12.7 the correct should be 0.30min different.

this is the current code:

strsql = "Update OverTime, Attendance " & _
        "Set OverTime.[match] = -([OverTime].[TimeFrom] - [Attendance].[Time])" & _
        "Where (OverTime.employeeno = [Attendance].[employeeno])" & _
       "AND (OverTime.overtimedate=[Attendance].[overtimedate]);"

please help.thanks.

[gracehskuo]

hi,please help.now i'm able get the time different.but then i facing the problem is when the data saved to the match fields is date format it suppose is "hh:nn:ss"saved to the Match field.The field data type is Date/Time format.

strsql = "Update OverTime, Attendance " & _
        "Set OverTime.[match]  = datediff('n',[Attendance].[Time],[OverTime].[TimeFrom])" & _
        "Where (OverTime.employeeno = [Attendance].[employeeno])" & _
       "AND (OverTime.overtimedate=[Attendance].[overtimedate]);"

please help Thanks.

[leinad31]

If your getting difference in date then you should be storing duration in pre-agreed time interval, e.g. minutes, rather than storing to a datetime data type. In short, column match shouldn't be in date time.