Solved

How to combine the Hours and Minutes from Datediff  in order to update access DB field?

Posted on 2006-07-22
2
340 Views
Last Modified: 2012-05-05
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
Comment
Question by:cubicalmonkey
2 Comments
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 125 total points
ID: 17160468
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
 

Author Comment

by:cubicalmonkey
ID: 17176811
Thanks Fritz,
I was able to figure everything out with your suggestions.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Import csv files to MS SQL 5 85
Downside of adding characters set in ASP pages 6 29
innerHTML 7 34
return false must be hit after calling certain command 10 33
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question