Solved

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

Posted on 2007-04-10
16
1,482 Views
Last Modified: 2013-02-07
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
Comment
Question by:khawkins96
  • 6
  • 6
  • 3
  • +1
16 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
ID: 18884200
NoOFHours:dateDiff("h", [Work_Begin_Time],[Work_End_Time])
0
 
LVL 7

Expert Comment

by:Imoutwest
ID: 18884210
NameOfField: DateDiff("h", [Work_Begin_Time],[Work_End_Time])
0
 
LVL 7

Expert Comment

by:Imoutwest
ID: 18884227
Should have hit refresh...
0
 
LVL 7

Expert Comment

by:Imoutwest
ID: 18884244
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

by:Rey Obrero
ID: 18884265
yes, it happens..don't worry about that
0
 

Author Comment

by:khawkins96
ID: 18884609
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

by:Rey Obrero
ID: 18884629
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

by:wiswalld
ID: 18886222
Me.TotalHours = Format([Work_Begin_Time] - 1 - [Work_End_Time], "Short Time")
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:khawkins96
ID: 18889441
capricorn1:

How would I apply that function in my query?

Thanks!
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 18889475
in a column in the query grid

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

Author Comment

by:khawkins96
ID: 18889514
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 18889564
< 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

by:khawkins96
ID: 18889589
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

by:Rey Obrero
ID: 18889704
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

by:khawkins96
ID: 18889757
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

by:khawkins96
ID: 18891097
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

932 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

14 Experts available now in Live!

Get 1:1 Help Now