Sum Time in Report

[abenitez77]

I have a report that has 2 fields (TimeIn and TimeOut). I am totaling the time for the day using : =Format(TimeSerial(0,((DateDiff("n",[TimeIn],[TimeOut]))),0),"h:nn")

In my report, I am grouping it by week and I need to sum the time at the report footer for that week. How can i do it? I tried doing Sum(text13). Text13 is the name of the textbox that has the calculation above totaling the time for the day.

 

[MarkK]

You might be able to replace this

=Format(TimeSerial(0,((DateDiff("n",[TimeIn],[TimeOut]))),0),"h:nn")

... with this, which is a little more readable ...

=Format([TimeOut] - [TimeIn],"h:nn")

... and then for the sum at the end ...

=Format(Sum([TimeOut] - [TimeIn]), "h:nn")

Maybe test that out and see what you get.
Cheers,

 

[abenitez77]

Your solution works for the total time for the day but not for the week...see results sample below:

TimeIn TimeOut totaltime
9:15 AM 6:30 PM 9:15
9:45 AM 4:45 PM 7:00
1:33 PM 5:40 PM 4:07
9:33 AM 4:33 PM 7:00

Total time 3:22

 

[MarkK]

The problem is that it rolls over the 24 hour mark. The actual math is 27:22 but a date variable, when you go over 24, increments the day and starts you over at midnight, so you're getting 3:22am. Technically correct.
I'll be back ...

 

[abenitez77]

this worked:
=Int(Sum([TimeOut]-[TimeIn])*24) & ":" & Format(Sum([TimeOut]-[TimeIn]),"nn")

 

[MarkK]

Nicely done!
For posterity, here's a function that'll do it...

Function HoursMinutes(ByVal interval As Double) As String
   Dim days As Long
   Dim var
   days = Int(interval)
   var = Split(Format(interval - days, "h:nn"), ":")
   HoursMinutes = days * 24 + var(0) & ":" & var(1)
End Function

... and your control source could look like ...

=HoursMinutes(Sum([TimeOut]-[TimeIn]))

 

[edsmom45]

I have a similar problem to the one above you just answered. I am also doing a Date/Time field in which I enter the time of day an individual clocks in and out. When I query individual times it gives me accurate hours and minutes for each individual. But when I try to do a total for a week or month in a report, the hours and minutes are correct on some and not on others. After seeing your explanation of the "over 24 hours" I think that is what is happening. I'm just not sure where to put the solution to the problem. Should it be in the query or the report, and where (or when) do I put it in the report. This would sure make my day easier for me to have the answer to this question.

 

[MarkK]

Hey there, welcome to the forum.
You haven't given enough specifics about your system for me to say exactly where this logic should go. Certainly somewhere you calculate hours and currently you get a wrong result. This logic should probably be a part of that calculation.
Where do you do this?

...when I try to do a total for a week or month...

Cheers,
Mark

 

[edsmom45]

Thanks Mark for your quick reply. I do a query that gives me each student's hours daily. I use Report Wizard to formulate my report. I group on the student's last name, then group on first name, summary options I Sum on total hours (summary only). I realized when I counted up the actual hours the students have that it does add correctly when it is under 24 hours. After that, it's just a mess. The minutes are always right, so I figured it was the "over 24 hours" deal, but I don't know how to correct it or where.

 

[edsmom45]

Just a note on the above, I have been making this work by Exporting the report to Excel, then formating the hours column to [h]:mm and it turns out right. I would like to not have to go through all that and just have it turn out right in the report.

 

[MarkK]

In post #6 there's code and a controlsource. Copy the code to your report. Copy the controlsource to the controlsource of a control in your report and replace "Sum([TimeOut]-[TimeIn])" with the name of your date control.

 

[edsmom45]

Thanks again Mark, but I already copied all that stuff from #6 and couldn't figure out where to put it in the report. That was what I was asking in the first place. I went to report design and tried putting the code (using my [clock out 1] and [clock in 1] in the control source for the sum total in the footer and I put it in exactly like it shows in #6 and it kept telling me that I had an extra (. I removed each of them (one at a time) and tried running and it still didn't work. I don't know where to put the function though. At what point in designing the report do I put this and where? Unfortunately I've learned most of what I know about Access in just doing it, and have not had a lot of training in report design (thus I use the wizard). I really need more specifics. Sorry to be such a pain.

 

[MarkK]

Open your report in design view.
Open a property sheet on something, anything.
Go to the events tab and double click a row.
- This adds [Event Procedure] to the row
Click the ellipsis (...) button on the far right of that row.
- This opens a code window, which is the module behind the form/report.
- This adds the signature for an event handler

This is where code goes. This'll make your life easier.
Cheers,
Mark

 

[edsmom45]

Well, here I am again. I did as you said and opened the properties sheet for the whole report. I came to the point of entering the function. I got to the line var = Split etc. and when I tabbed to go to the next line a box opened and said "Compile Error: Expected: list separator or)" and the line turned red and the : in the ":" turned blue. I tried to put the rest of the function in, which it allowed me to do, but that entire line stayed red. Then I went to the Sum Total on the "First Name" line and went to the control source and typed in the code using my field names. When I ran the report the Sum line of each person had a #error in it and the totals were the same as always. Not sure what it is I'm doing wrong. Is there any way I can copy my file or report to you for you to evaluate? I so appreciate your help. Also how do I show you parts of my report or the exact things I have entered and where I entered them?

 

[MarkK]

You could post your database. But I think you need ten posts.
You're so close though. That line that stayed red. I has to be a typo. Maybe try to highlight the code on the web page and copy it to your module so there aren't errors.
Also, go to the WaterCooler section of the site and run up your post count.
Cheers,
Mark

 

[edsmom45]

OK, here we go again. I copied the codes straight from the post, entered them, and ran the report, still getting the same thing. I have an individual who has 200 hours, 39 minutes and it comes up 8:39. Frustration is a mild word for what I'm feeling. I only have 5 posts (this will make 6), so don't know what else to do. Thank you again for being so patient. You are the first person that has even given me the time of day on this issue. Most people just say, "I don't like Access, ask someone else". So I really appreciate all your help. This is what the field in the query that I draw this information from looks like: Total Hours: Sum([Clock out 1]-[clock in 1]).

Attendance by Clock In Date Last Name First Name Day or Night Clock In 1 Clock Out 1 Reason for Absence 2/28/2011 Dryer Paula N 18:00 20:35 (This part keeps changing when I try to send it to you).

Clock in and Clock out are formatted as Date/Time,short time.

I pasted this: =HoursMinutes(Sum([clock out 1]-[clock in 1])) into the Details Total Hours control source of the report.

I pasted this:
Option Compare Database
Private Sub Report_Current()
Function HoursMinutes(ByVal interval As Double) As String
Dim days As Long
Dim var
days = Int(interval)
var = Split(Format(interval - days, "h:nn"), ":")
HoursMinutes = days * 24 + var(0) & ":" & var(1)
End Function
in the "On Current" line of the Report form properties Event tab.

That is all the information I know to give you. Thanks bunches and bunches. Oh, and where do I find the Watercooler, which tab?

 

[edsmom45]

Here I am back again. I did all the things suggested. I copied the code straight from the post and that got rid of the red line. I put it in the report properties event line like you said. It still didn't work. I'm so bummed. I only have 6 posts (including this one). By the way, where is the Watercooler, I couldn't find that either.

I hope you don't give up on me because I sure would like to get this report taken care of. Thanks again for all your time.

 

[MarkK]

You still working on this? Post count up to 10 yet? It's really easy to post a few more times.
Cheers,

 

[edsmom45]

Yep, I'm still working on it. I sent you a post yesterday. I think it was the same as the one I sent last week, but anyway. I guess I can just say hi two more times and I'll be eligible to post my data base. You'll need to explain to me how to do that. The other thing that concerns me is this data base has student's social security numbers in it. Is there anyway I can protect that field? I'm not concerned about you, but I'm not sure how many other people would have access to the data base since this is being posted on a forum. Can you give me an idea how to do that?

 

[MarkK]

You can delete them all. Do something like ...

currentdb.execute "UPDATE YourTableName SET YourSensitiveField = 0"