Solved

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

Posted on 2007-04-10
1,480 Views
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!
0
Question by:khawkins96
• 6
• 6
• 3
• +1

LVL 119

Accepted Solution

Rey Obrero earned 250 total points
NoOFHours:dateDiff("h", [Work_Begin_Time],[Work_End_Time])
0

LVL 7

Expert Comment

NameOfField: DateDiff("h", [Work_Begin_Time],[Work_End_Time])
0

LVL 7

Expert Comment

Should have hit refresh...
0

LVL 7

Expert Comment

Capricorn1, when I first click on the Question it doesn't always show the first comment, have you experience this? Imoutwest
0

LVL 119

Expert Comment

yes, it happens..don't worry about that
0

Author Comment

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

LVL 119

Expert Comment

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

LVL 13

Expert Comment

Me.TotalHours = Format([Work_Begin_Time] - 1 - [Work_End_Time], "Short Time")
0

Author Comment

capricorn1:

How would I apply that function in my query?

Thanks!
0

LVL 119

Expert Comment

in a column in the query grid

NoOFHours:ElapsedTime([Work_End_Time]- [Work_Begin_Time])
0

Author Comment

capricorn1:

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

LVL 119

Expert Comment

< 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

Author Comment

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

LVL 119

Expert Comment

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

Author Comment

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

Author Comment

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

## Featured Post

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…