Solved

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

Posted on 2007-04-10
16
1,485 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 3
  • +1
16 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 

Author Comment

by:khawkins96
ID: 18889441
capricorn1:

How would I apply that function in my query?

Thanks!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

749 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