Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

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)
0
cubicalmonkey
Asked:
cubicalmonkey
1 Solution
 
fritz_the_blankCommented:
I think that the issue here is that the total overtime is not really a date but a number of minutes or hours.  I suppose that you could create a date instead by trying to insert something like:

 TotalOTMins = DateAdd("n",TotalM,"2006/07/22 00:00:00")

and then you could pull out the overtime that way.

If I were doing this, however, I would change the  TotalOTMins  to an integer field.

FtB
0
 
cubicalmonkeyAuthor Commented:
Thanks Fritz,
I was able to figure everything out with your suggestions.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now