cubicalmonkey
asked on
How to combine the Hours and Minutes from Datediff in order to update access DB field?
I have an ASP which is logging Overtime for employees. It pulls the ClockInTime and ClockOutTime from an Access DB.
The page uses datediff to find the hours and minutes between ClockInTime and ClockOutTime.
When the employee clock's out, I display the total time in Hours and Minutes they worked.
I also want to insert this to the TotalOT field in my database.
When I try updating the TotalOT field in my table, I keep getting the "Data type mismatch.." error.
I think this is because datediff is splitting up hours and Minutes.
I tried just using minutes rather than attempting to combine Hours and Minutes, but that still returns the data type error.
The TotalOT field is set as Date/Time - short
Does anyone know how I could combine the Hours and Minutes from Datediff so I could update this DB field?
sql = "UPDATE TimeSheet SET TotalOTMins ='" & TotalM & "' WHERE NAME = ('"& Name &"') AND DT = (#" & todaysDate &"#) AND isNull(TotalOTMins)"
'Pull clock in / out times from database
Cout = rs("ClockOutTime")
Cin = rs("ClockInTime")
'Format Date so we can get the amount of OT worked
Cin = FormatDateTime(Cin, vbShortTime)
Cout = FormatDateTime(Cout, vbShortTime)
Display the total OT worked for this user's session
response.write "You have worked " & TotalH & ":" & TotalM & " Hours/Minutes of overtime for this session."
set rs = connection.execute(sql)
The page uses datediff to find the hours and minutes between ClockInTime and ClockOutTime.
When the employee clock's out, I display the total time in Hours and Minutes they worked.
I also want to insert this to the TotalOT field in my database.
When I try updating the TotalOT field in my table, I keep getting the "Data type mismatch.." error.
I think this is because datediff is splitting up hours and Minutes.
I tried just using minutes rather than attempting to combine Hours and Minutes, but that still returns the data type error.
The TotalOT field is set as Date/Time - short
Does anyone know how I could combine the Hours and Minutes from Datediff so I could update this DB field?
sql = "UPDATE TimeSheet SET TotalOTMins ='" & TotalM & "' WHERE NAME = ('"& Name &"') AND DT = (#" & todaysDate &"#) AND isNull(TotalOTMins)"
'Pull clock in / out times from database
Cout = rs("ClockOutTime")
Cin = rs("ClockInTime")
'Format Date so we can get the amount of OT worked
Cin = FormatDateTime(Cin, vbShortTime)
Cout = FormatDateTime(Cout, vbShortTime)
Display the total OT worked for this user's session
response.write "You have worked " & TotalH & ":" & TotalM & " Hours/Minutes of overtime for this session."
set rs = connection.execute(sql)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was able to figure everything out with your suggestions.