Time Calculations

[jediknight]

Hi all, Like a lot of others I am still quite new at this so could an
replies not include reference to VBA as I do not hav the first clue.
The Problem: My main table includes [Time In] & [Time Out]. I hav
created a query showing time in, time out & the difference in both tim
(Total hrs).

I have then created a report from that query but have added a text bo
showing (Total hrs) twice, one with the actual figure, the other givin
a record by record total (Running sum).

As you have probable guessed, when the total hrs exceeds 23:59, i
reverts back to 00:00.

Can anyone tell me how I can get the hours to count above 24?

The time in and time out formats are set to General date.

Many Thanks in advance

 

[StCyrM]

If you sum Date/Time fields, Access collects the result as a Date/Time since
day zero.

You can get the results in minutes like this:
=DateDiff("n", #0:00#, Sum([YourDateTimeField])

Use integer division to get the result in hours, and Mod to get the
remaining minutes:
=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

 

[jediknight]

Correct me if I am wrong but that would give me a "Grand total" if I wa
totalling hours say in the page footer. What I am really after is
running sum so that the total goes up in relation to the "total hrs"

example:
TIME IN____________TIME OUT___________TOTAL___TOTAL 2
21/11/2003 21:55____22/11/2003 06:05____08:10____08:10
22/11/2003 22:00____23/11/2003 06:05____08:05____16:15
23/11/2003 22:00____24/11/2003 06:05____08:05____24:20
24/11/2003 22:00____25/11/2003 06:08____08:08____32:28

Does this explain it better?

Thanks again. (This is so frustrating

 

[John Vinson]

Correct me if I am wrong but that would give me a "Grand total" if I was
totalling hours say in the page footer. What I am really after is a
running sum so that the total goes up in relation to the "total hrs"

example:
TIME IN____________TIME OUT___________TOTAL___TOTAL 2
21/11/2003 21:55____22/11/2003 06:05____08:10____08:10
22/11/2003 22:00____23/11/2003 06:05____08:05____16:15
23/11/2003 22:00____24/11/2003 06:05____08:05____24:20
24/11/2003 22:00____25/11/2003 06:08____08:08____32:28

Does this explain it better?

Thanks again. (This is so frustrating)

I think part of the problem is that you're apparently using a
Date/Time value for a duration, and summing it. This will NOT work
well. Date/Time values correspond to a particular moment in time -
they're stored as a Double Float number, a count of days since
midnight, December 30, 1899. A Time value will NOT display as 32:28 -
that value will actually be stored as 1.3527777777777777, and
displayed as #31-Dec-1899 08:28:00#.

I'd suggest instead using the DateDiff function to calculate the
difference in minutes between TIME IN and TIME OUT:

DateDiff("n", [TIME IN], [TIME OUT])

You can sum these times in minutes. It's easy to get a running sum on
a Report - just display the value in a textbox with its Running Sum
property set to TRUE - but a fair bit snarkier in a Query; you'll need
to use DSum() to sum all the records prior to this record.

You can convert 1948 minutes to 32:28 with an expression like

[Duration] \ 60 & Format([Duration] MOD 60, ":00")