Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 902
  • Last Modified:

Would like to display total time (hours and minutes) where the number of hours exceeds 24 hours

Hello,
I am using Access 2007 and I ma having a time with the format.  I have tried to display the sum of hours where the number of hours exceed 24 hours.  There is one accumulation of hours which should total 130:30 for Contract.  Instead, I get 10:30.  Likewise, I should also get 128:00 total hours for GSC Recording, instead, I get 8:00.

I am using an excel spreadsheet at the table.  I have given the field properties for the Data Type of Date/Time the format of:  \[hh\]:nn

The calculated hours is the same format.
The query that I am using displays the sum of the Calculated hours based on the grouping of type and this is where I would like for when the total is greater than 24 hours to show.  Can you assist me please?  Thank you.
0
mtrout
Asked:
mtrout
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Take a look at the following Office Article:

http://office.microsoft.com/en-gb/access-help/on-time-and-how-much-has-elapsed-HA001110218.aspx?CTT=5&origin=HA001054662

It contains the function HoursAndMinutes(), which you can paste into a module and then call from anywhere including the query.

A handy reference to is:

http://office.microsoft.com/en-gb/access-help/using-dates-and-times-in-access-HA001054662.aspx

 For dates and times in general and working with them.
Jim.
0
 
hnasrCommented:
List few records and expected output, and what are the wrong results.
0
 
Gustav BrockCIOCommented:
This function fits the purpose. Sum the time and use this for the parameter datTime:
Public Function FormatHourMinute( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String
  
' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03# + #20:01#
'   returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinute     As String
  Dim strHourMinute As String
  
  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute count when needed.
  strMinute = Right("0" & CStr(Minute(datTime)), 2)
  strHourMinute = strHour & strSeparator & strMinute
  
  FormatHourMinute = strHourMinute
  
End Function

Open in new window

/gustav
0
Technology Partners: 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!

 
David Johnson, CD, MVPOwnerCommented:
use the format dd-hh:mm instead
0
 
mtroutAuthor Commented:
Thank you.  To be honest, for what ever reason, I just don't get it.  I'm not getting it into my head as to what to do.  I'll just read about functions, modules, etc,  again and hopefully something will click.  I was hoping for something that could be straight forward and much easier solution.  

I'm sure that you have explained it in simple terms, but it's not just not coming through at the moment.  I will step back, and hopefully try again later.  Thank you kindly.
0
 
Gustav BrockCIOCommented:
Create a new module. Save it as, say, basDateTime.
Copy and paste the function above into the module.
Go to menu, Compile and Save.

In your query, have a field using an expression like this:

TotalHours: FormatHourMinute(Sum[YourDateField]))

/gustav
0
 
mtroutAuthor Commented:
Gustav,

              Thanks, but I'm getting syntax erros all over the place.  I'm getting that the expression I entered contains invalid syntax.  I have been trying everywhere inteh design of the query and finally tried putting what you wrote into the field.  It's not working.  I could probably do better using crystal reports, or attempt designing a report on the Access side, but I'm spinning alot of wheels here and it's not comming together.  I have enclosed a screen shot. I ahve enclosed the data base including the table and query when I first asked so that if anyone wanted to look around they could.  If you choose to reply and I still don't get it...I'm not going to get it.  Please don'e be offended.  Thanks for your help.
0
 
Gustav BrockCIOCommented:
We miss the screenshot.

/gustav
0
 
mtroutAuthor Commented:
sorry about that.  the name of the screenshot is NoClue.doc

Also, could the problem be that I should NOT include a calculated field in the Excel spreadsheet and then import to Access?  The Excel spreadsheet shows the correct calculation for the field.  Once it's imported to Access, that calculated field goes to the wayside.
NoClue.doc
Meetings-Log-2012-MT.xls
0
 
Gustav BrockCIOCommented:
Sorry, a small typo. It should read:

TotalHours: FormatHourMinute(Sum([YourDateField]))

You could perhaps better use:

TotalHours: FormatHourMinute(Sum([End Time]-[Start Time]))

or, if you recieve strings from the sheet:

TotalHours: FormatHourMinute(Sum(CDate([End Time])-CDate([Start Time])))

/gustav
0
 
mtroutAuthor Commented:
Gustav,

 Thank you, Thank you, Thank you so much!!  This worked!!!

TotalHours: FormatHourMinute(Sum([YourDateField]))

The column named SumOfCalculated Hours will be removed.  That is how it looked before.  The totalHours is what you assisted me with.  The attached shows the before and after.
Results.doc
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now