Solved

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

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now