Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-04-10
16
Medium Priority
?
1,510 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 1000 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

610 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