Solved: Access 2010 Total Time and Date Values

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
Hello guys,

I have a report which has (User, Office, Problem, Date time open, Date Time Close, and Total Date time of job.) TotalDateTime calcultates the time from when it was open to when it closes.

The report shows the total of all jobs so i can print it out. At the bottom of the report i want it to tell me the total time of all the TotoalDateTime records, i.e:

Joe Bloggs Open-11/06/2011 10:30:00 Close-12/06/2011 11:25:00 Total- 1 Day, 55 Mintutes
Jack Black Open-11/06/2011 10:30:00 Close-12/06/2011 11:25:00 Total- 1 Day, 55 Mintutes

TotalOverPeriod - 2 Days, 1 Hour, 50 Mintues

I cant seem to make it total up...Anyone know how to do this? :confused:

Thank you
 

OBP

Joined
Mar 8, 2005
Messages
19,895
As you have found it is not straight forward because you have to add the minutes first to get the hours to be added to the hours total, then you have to calculate the hours to get the days to add to the days total and then calculate the days total.
It may be possible using queries, but if not it will need VBA code.
 

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
Gosh that sounds a bit mad! I had to do something simular to get it to calculate the difference from open to close using the following code: (Something i found on Microsoft, but it does not really help me with what im trying to do - i dont think?)

Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
'*********************************************************************
' Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
' Returns the time elapsed between a starting Date/Time and an ending
' Date/Time formatted as a string that looks like this:
' "10 days, 20 hours, 30 minutes, 40 seconds".
'*********************************************************************
Dim interval As Double, str As String, days As Variant
Dim hours As String, minutes As String, seconds As String
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function
interval = dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
hours = Format(interval, "h")
minutes = Format(interval, "n")
seconds = Format(interval, "s")
' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & minutes & seconds <> "000", ", ", " "))
' Hours part of the string
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))
' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", minutes & " Minutes"))
str = str & IIf(minutes = "0", "", IIf(seconds <> "0", ", ", " "))
' Seconds part of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", seconds & " Seconds"))
ElapsedTimeString = IIf(str = "", "0", str)
End Function
 

OBP

Joined
Mar 8, 2005
Messages
19,895
You can probably use elements of that code to do the other calculation, but you would need to loop through all the records within a Group.
It may be possible to do this with queries, but break the data up in to separate fields ie days and time. You would then total the Time and the days, then on the Report add the days created by the time to the days from the query.
If you could provide with some Dummy data in a zipped Access 2003 format database I can take a look for you.
 

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
(DB Attached)
Hello, i've been doing more working out...and ive managed to get it to sum up the time, but...it displays it as hours and minutes.
However...this is where it gets slightly more complicated...The report which i have left in the DB i need to total the time, but with the days equal to only 10 hours/8am to 6pm. This is because when my boss wants to produce a report on how long a server has been off line for, he only wants to calculate it for the working day 8-6.

So, on a report for QrySystemDowntime i need a total time counting days as 8am-6pm,

and a report for QryClientSupport to total the time, but i want it to be displaying as it does in the TotalDataTime field.

I was doing some google searching for VBA to make the Day equal to so many hours, but i dont understand how to make it work for me, and how to use it...or if i can use part of it for what im trying to do:

Sub Add_Hours_Version1()

Const HOURS_PER_DAY As Long = 8
Dim StartDate As Date
Dim NumDays As Long
Dim HoursToAdd As Long
Dim HolidaysList() As Variant
Dim holiday As Variant
Dim bWasFound As Boolean

' edit/add/delete as needed
' these are US Federal Holidays
' taken from http://www.opm.gov/Operating_Status_Schedules/fedhol/2009.asp
HolidaysList = Array("1/1/2009", "1/19/2009", "2/16/2009", _
"5/25/2009", "7/3/2009", "9/7/2009", _
"10/12/2009", "11/11/2009", "11/26/2009", "12/25/2009")

StartDate = InputBox("Enter start date")
HoursToAdd = InputBox ("Enter number of hours")

' round up number of days
NumDays = WorksheetFunction.Ceiling(HoursToAdd / HOURS_PER_DAY, 1)

' increase date
StartDate = StartDate + NumDays

' check if it's a holiday
For Each holiday In HolidaysList
If holiday = StartDate Then
bWasFound = True
Exit For
End If
Next holiday

If bWasFound Then
StartDate = StartDate + 1
End If

' check if it's a weekend, if so then move it to Monday
If Weekday(StartDate, vbMonday) = 6 Then
StartDate = StartDate + 2
ElseIf Weekday(StartDate, vbMonday) = 7 Then
StartDate = StartDate + 1
End If
End Sub
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
You say "The report which i have left in the DB i need to total the time, but with the days equal to only 10 hours/8am to 6pm"
So does that mean that you first have to count only the times between 8am & 6pm?
ie if the 2 date/times are Open-11/06/2011 10:30:00 Close-12/06/2011 23:25:00 is 1 day 7.5 hours
 

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
Yes that is correct, (I guess i gave a bad example for times there - as it we would not be in work at 2300 hours)

So Open-11/06/2011 10:30:00 Close-12/06/2011 13:30:00 = 1 Day(=10Hours) and 3 Hours

Equaling 13 Hours Total Work time elapsed of downtime.

I think i worked that to be correct! :eek:
 

OBP

Joined
Mar 8, 2005
Messages
19,895
OK, here is how I would do it using just a query with calculating fields which can then be "Summed" on a report or in another query.

I will need to think about the weekend part.
 

Attachments

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
Wow...i can see where your going with this, was does Int mean? Ive had a quick gander because my head is still in DB mode from work.

Im going to have to sit down and go through it understanding it, but im not back into work untill monday, so ill pick it up then...thankyou for your time on this...it is very much appreciated, and im sure anybody else who is tracking this will be just as grateful, as there is useful info in here for people. :)
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Int means the Integer (whole number) part of the number. So int gives you the whole number and original value mins Int(value) gives you the remainder. I think that there is also a function for that as well, but I can't remember what it is.

ps Nope, can't find a remainder function.
 

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
The process works okay when you have a day in the workings, but if you have no day, then it doesnot seem to addup the hours and mins properly, ive added a few more dummy entries to show. What does +0.5 mean?
 

Attachments

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
Actually it does not seem to have any difference with the days in. The Minute goes to a Minus, to indicate to take away from the hour field
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top