MS Access Query - calculate number of hours (military time)

I have a work begin time and work end time field that each contain the date and time (military time). I need to calculate the number of hours between the two. For example:
Work_Begin_Time  4/10/2007 5:04:13 PM
Work_End_Time    4/10/2007 6:34:13 PM

Thoughts on how to do this in a query?

Thanks!
khawkins96Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
NoOFHours:dateDiff("h", [Work_Begin_Time],[Work_End_Time])
0
 
ImoutwestCommented:
NameOfField: DateDiff("h", [Work_Begin_Time],[Work_End_Time])
0
 
ImoutwestCommented:
Should have hit refresh...
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ImoutwestCommented:
Capricorn1, when I first click on the Question it doesn't always show the first comment, have you experience this? Imoutwest
0
 
Rey Obrero (Capricorn1)Commented:
yes, it happens..don't worry about that
0
 
khawkins96Author Commented:
Capricorn1:

I forgot one thing, what do I need to do to account for something that is less than 1 hour. For example:

Begin time 4/10/2007 5:00:27 PM
End time4/10/2007 5:45:27 PM

This is going to be 45 min so I will need to calculate hours and minutes.

Thanks!
0
 
Rey Obrero (Capricorn1)Commented:
you will need a function to do that
from m$ http://support.microsoft.com/default.aspx?kbid=210276

'------------------------------------------------------------------
'  This function calculates the elapsed time between two values and then
'  formats the result in four different ways.
'
'  The function accepts interval arguments such as the following:
'
'     #5/12/95 6:00:00AM# - #5/11/95 10:00:00PM#
'
'    
'
'     [End Time]-[Start Time]
'------------------------------------------------------------------

Function ElapsedTime (Interval)
  Dim x
  x = Int(CSng(Interval * 24 * 3600)) & " Seconds"
  Debug.Print x
  x = Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss") _
     & " Minutes:Seconds"
  Debug.Print x
  x = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nn:ss") _
     & " Hours:Minutes:Seconds"
  Debug.Print x
  x = Int(CSng(Interval)) & " days " & Format(Interval, "hh") _
     & " Hours " & Format(Interval, "nn") & " Minutes " & _
     Format(Interval, "ss") & " Seconds"
  Debug.Print x

End Function

0
 
wiswalldCommented:
Me.TotalHours = Format([Work_Begin_Time] - 1 - [Work_End_Time], "Short Time")
0
 
khawkins96Author Commented:
capricorn1:

How would I apply that function in my query?

Thanks!
0
 
Rey Obrero (Capricorn1)Commented:
in a column in the query grid

NoOFHours:ElapsedTime([Work_End_Time]- [Work_Begin_Time])
0
 
khawkins96Author Commented:
capricorn1:

Thanks for your continued assistance.

I'm receiving an error when I run the query - undefined function 'ElapsedTime' in Expression. I copied exactly what you listed above and my module is named ElapsedTime.

Thoughts?
0
 
Rey Obrero (Capricorn1)Commented:
< my module is named ElapsedTime> this is a big mistake never name a module with the same name as the function or sub

rename you module to  modElapsedTime
0
 
khawkins96Author Commented:
Thank you for that advice. I do appreciate it.

I renamed the module and the following is in my query...

NoOFHours: modElapsedTime([Work_End_Time]-[Work_Begin_Time])

However, I'm still receiving the same error. Is there anything I need to do to the module itself? I copied exactly what you had listed above.

Thoughts?
0
 
Rey Obrero (Capricorn1)Commented:
khawkins96,
you should change the name of the module not the function.


copy this and replaced the codes from the module modElapsedTime

Function ElapsedTime (Interval)
  Dim x
  x = Int(CSng(Interval * 24 * 3600)) & " Seconds"
 
  x = Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss") _
     & " Minutes:Seconds"
 
  x = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nn:ss") _
     & " Hours:Minutes:Seconds"
 
  x = Int(CSng(Interval)) & " days " & Format(Interval, "hh") _
     & " Hours " & Format(Interval, "nn") & " Minutes " & _
     Format(Interval, "ss") & " Seconds"
 ElapsedTime= x

End Function

then use this in your query
NoOFHours: ElapsedTime([Work_End_Time]-[Work_Begin_Time])

0
 
khawkins96Author Commented:
Oops! Sorry about that! I got a little confused.
I did exactly what you suggested and the query ran, however I'm getting a #Error in the field. The data in the two fields are:

WORK_BEGIN_TIME 4/10/2007 5:04:13 PM
WORK_END_TIME 4/10/2007 6:34:13 PM

There's also another one:
WORK_BEGIN_TIME 4/10/2007 5:00:27 PM
WORK_END_TIME 4/10/2007 5:45:27 PM

Both are displaying #Error. Any ideas?

0
 
khawkins96Author Commented:
Thanks for all the help! I took your very first suggestion and did some more searching on the internet. I ended up with the following:

TimeInMinutes: DateDiff("n",[Work_Begin_Time],[Work_End_time])
HoursTime: [TimeInMinutes]\60
MinutesTime: [TimeInMinutes] Mod 60
0
All Courses

From novice to tech pro — start learning today.