Solved

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

Posted on 2006-07-22
2
336 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 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