compare Time between 2 table
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:
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.
Re: compare Time between 2 table
Above code will only work if there are no day spanning OT, e.g. no midnight shift OT. Try including date in DateDiff().
Re: compare Time between 2 table
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.
Code:
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.
Re: compare Time between 2 table
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.
1 Attachment(s)
Re: compare Time between 2 table
leinad31;
hi,please help.when i run the query have error appear.
this is my current code:
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"
Re: compare Time between 2 table
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.
Code:
UPDATE OverTime INNER JOIN Attendance
ON (OverTime.overtimedate = Attendance.overtimedate)
AND (OverTime.employeeno = Attendance.employeeno)
SET OverTime.[match] = -([OverTime].[TimeFrom]=[Attendance].[Time]);
Code:
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.
Re: compare Time between 2 table
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:
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.
Re: compare Time between 2 table
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.
Code:
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.
Re: compare Time between 2 table
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.