Summing Time on a Report (1 Viewer)

AnnMV888 · Oct 5, 2011

I have the following in my query:

Late: IIf([dtmLogTime]>[dtmStartTime],DateDiff("n",[dtmStartTime],[dtmLogTime])\60 & Format(DateDiff("n",[dtmStartTime],[dtmLogTime]) Mod 60,"\:00"),"")

I then list the Late field on my report. Now I have to Sum this field on my report and can not figure out how. The Late field in my query is formatted as medium time. Can anyone help me? Thanks in advance.

vbaInet · Oct 5, 2011

Do you know how to form any of the other fields in your report? Place a textbox in the footer section and type =Sum([Late])

Or is this not what you're talking about?

AnnMV888 · Oct 5, 2011

That is exactly what I am talking about and it was the very first thing I tried. I placed it in the name footer so each persons total would appear at the end of their time. Listed below is what I received and it went on from there. I tried many different ways from what I found online but none of them work.

This expression is typed incorrectly, or it is too complect to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

vbaInet · Oct 5, 2011

So is it showing #Error?

Show me an example of that field's output.

AnnMV888 · Oct 5, 2011

No. The report doesn't even open. I open the report, the query prompts for the year, the message comes up without the report behind it. I click OK on the message and it goes away and I am still at the database window form object. If I were to remove this textbox the report would open and each person's late minutes would show up in the detail. I have a textbox to count these items and it's fine but the Sum just won't work.

vbaInet · Oct 5, 2011

You're not giving enough information. What Year are you referring to? I don't see any reference to a year field in your code. This must be something else within your report.

AnnMV888 · Oct 5, 2011

I have the following in my query:

Late: IIf([dtmLogTime]>[dtmStartTime],DateDiff("n",[dtmStartTime],[dtmLogTime])\60 & Format(DateDiff("n",[dtmStartTime],[dtmLogTime]) Mod 60,"\:00"),"")

This creates a new field in my query called [Late]. My query also wants me to enter a year (2011) when it runs so I only get this years data. That is:

Date: DatePart("yyyy",[dtmLogDate]) and it prompts me to enter a year in the criteria.

My report has the [Late] field in the details section and the count of the [Late] field and the sum of the [Late] field in the footer.

vbaInet · Oct 5, 2011

You shouldn't use Date as a field name. It is reserved for Access and VBA.

Let me see a stripped down version of your db.

AnnMV888 · Oct 5, 2011

OK. I will change the name to something else. I also have to clean up the data and add false data so it will take me a bit of time. Thank you for your help.

AnnMV888 · Oct 13, 2011

Sorry it took so long but I got involved in an upgrade I am working on and didn't have time. I hope you can still help but if you can't I understand.

vbaInet · Oct 13, 2011

That's fine!

AnnMV888 · Oct 26, 2011

I still do need to know how to do this if anyone can help me. I did upload a copy of the database to this site if that will help.

vbaInet · Oct 27, 2011

AnnMV888 said:
I still do need to know how to do this if anyone can help me. I did upload a copy of the database to this site if that will help.

Erm where?

AnnMV888 · Oct 27, 2011

I don't know what happened to it. This is the first time I have used this site. Do they delete them if they sit there too long?

I did upload it again, maybe you can look again?

vbaInet · Oct 27, 2011

Oh yea, you need to have had 10 posts to be able to upload your db. You've got 10 posts now so you can upload it in your next post.

AnnMV888 · Oct 28, 2011

Oh, OK. Let me try it again. Thanks.

vbaInet · Oct 28, 2011

Ok, so since you've got the code already, you can use the following to Sum them up:

Code:

=Sum(DateDiff("n",[dtmStartTime],[dtmLogTime]))\60 & Format(Sum(DateDiff("n",[dtmStartTime],[dtmLogTime])) Mod 60,"\:00")

AnnMV888 · Oct 28, 2011

That did exactly what I needed. Thank you so much for the help.

vbaInet · Oct 28, 2011

No problemo!

Summing Time on a Report (1 Viewer)

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread