Sum on time on report

[Bogdan Zamfir]

Hi,

I have a projrct managememnt application, with tasks and start and end time
for the task

I need to make a report with several tasks, and to sum the total time in
report footer

So I have in details band three textboxes

[Start time] [End time] [Task Time]

where [Task time] has controlsource =[End time]-[Start time]

All boxes are formatted as Short time, so [Task time] display the time as
appropriate (01:30, 00:50, etc)

I want to add a grand total in my report's footer, so I added a box with the
following control source

=sum([task time])

This gives me in report the message #Error

I tried to use =sum([End time]-[Start time]) (also, formattred as short
time), but this always return me 00:00

What I did wrong?
Any suggestion will be appreciated.

Regards,
Bogdan

 

[Wayne Morgan]

Place a 2nd textbox in the detail section and set its Visible property to No. Give it the
same Control Source as Task Time and set its Running Total to Over All. In the report
footer, set your total textbox equal to this new textbox.

Do you have any Start/End times that may cross over midnight? If so, you may need to
change the way you are handling this.

 

[Bogdan Zamfir]

Hi,

Place a 2nd textbox in the detail section and set its Visible property to No. Give it the
same Control Source as Task Time and set its Running Total to Over All. In the report
footer, set your total textbox equal to this new textbox.

I tried this too, but I got the same result.

Do you have any Start/End times that may cross over midnight? If so, you may need to
change the way you are handling this.

No, I don't have

Finally, I discovered the problem. Total hours on my test data was EXACTLY
(!!!) 24 hrs, but it seems Access can manage time only between 00:00 and
23:59:59, then it get to 00:00 again (it woeks only module 24 hrs). So it
cannot add several times intervals if total is greater then 24 hrs.

I found a workaround for this problem, using a custom made function,
FormatTime, who receives two INT parameters, number of hours and number of
minutes, and comvert minutes to hours and return a string formatted as hour
/ minute

Like this

FormatTime(12, 88) returns 13:28
It works also over 24 hrs, so formattime(23, 90) returns 24:30

And on report footer I used a textbox with this expression

=FormatTime(sum(hour(MyHoursFld));sum(minute(MyHoursFld)))

This solved my problem

Regards,
Bogdan

--
Wayne Morgan
Microsoft Access MVP

Hi,

I have a projrct managememnt application, with tasks and start and end time
for the task

I need to make a report with several tasks, and to sum the total time in
report footer

So I have in details band three textboxes

[Start time] [End time] [Task Time]

where [Task time] has controlsource =[End time]-[Start time]

All boxes are formatted as Short time, so [Task time] display the time as
appropriate (01:30, 00:50, etc)

I want to add a grand total in my report's footer, so I added a box with the
following control source

=sum([task time])

This gives me in report the message #Error

I tried to use =sum([End time]-[Start time]) (also, formattred as short
time), but this always return me 00:00

What I did wrong?
Any suggestion will be appreciated.

Regards,
Bogdan