Grand Total of Time in Report Footer not working

[Deano]

I am having a problem totaling the total hours worked.

I have a query with a number of fields includes dates and times plus a
calculation that calculates the total time (spent on a job). I then use
this query on a report and place the Calculated field on the details line.
I then have a Grand Total in the Report Footer which totals all the
calculated times for each job.

Here's how the Table/Query are setup
The table which the queried fields refer to are all standard text fields
plus all time field properties set as :- Date/Time , Short Time, Input Mask
00:00;0;_ which are fields 'StartTime' and 'FinishTime'.
The calculation field in the query is : TotalTime:
Format(TimeValue([finishtime])-TimeValue([starttime]),"h:nn").
The GrandTotal 'Control Source' in the Report Footer is set to
=Sum([TotalTime])

When I run the report, I get the message "The expression is typed
incorrectly, or it is too complex to be evaluated"

I have tried many different ways, even making a table out of the queried
results and summing on the fields but still get the same problems. Can
anyone help.

If more info is need, please let me know

Many thanks in Advance

 

[Duane Hookom]

Don't format TotalTime in the query. I would convert this to the number of
minutes:

TotalTime: DateDiff("n",TimeValue([starttime]),TimeValue([finishtime]))
If starttime and finishtime are date/time fields with no date portion then
you could eliminate the TimeValue()s.

Summing TotalTime in your report footer will give you the total number of
minutes. Divide by 60 to get the number of hours.

You may also need to handle possible Null start or finish times.

 

[Deano]

Thanks for response which most of it works fine.

What I can't manage to do is the summing in the report footer. My total
works out at 790 total minutes and divide by 60 gives out 13.166667 which
isn't correct. I've tried =Format(TimeValue(Sum([TotalTIme])),"h:nn") but
I just get ERROR displayed. Is there another way to convert the Report
Footer Totals into Hours:Mins.

Many Thanks again

Don't format TotalTime in the query. I would convert this to the number of
minutes:

TotalTime: DateDiff("n",TimeValue([starttime]),TimeValue([finishtime]))
If starttime and finishtime are date/time fields with no date portion then
you could eliminate the TimeValue()s.

Summing TotalTime in your report footer will give you the total number of
minutes. Divide by 60 to get the number of hours.

You may also need to handle possible Null start or finish times.

--
Duane Hookom
MS Access MVP
--

I am having a problem totaling the total hours worked.

I have a query with a number of fields includes dates and times plus a
calculation that calculates the total time (spent on a job). I then use
this query on a report and place the Calculated field on the details
line. I then have a Grand Total in the Report Footer which totals all the
calculated times for each job.

Here's how the Table/Query are setup
The table which the queried fields refer to are all standard text fields
plus all time field properties set as :- Date/Time , Short Time, Input
Mask 00:00;0;_ which are fields 'StartTime' and 'FinishTime'.
The calculation field in the query is : TotalTime:
Format(TimeValue([finishtime])-TimeValue([starttime]),"h:nn").
The GrandTotal 'Control Source' in the Report Footer is set to
=Sum([TotalTime])

When I run the report, I get the message "The expression is typed
incorrectly, or it is too complex to be evaluated"

I have tried many different ways, even making a table out of the queried
results and summing on the fields but still get the same problems. Can
anyone help.

If more info is need, please let me know

Many thanks in Advance

 

[Deano]

Sorted the problem out

After searching again through the posts, I found a similar problem in a post
under formscoding which gave a conversion into hrs:mins using the Format
value. Here it is

=Format((Sum([TotalTIme]))/(24*60),"hh"" H ""nn"" M""")

and it works fine.

I thought I would put the solution here as I know how awkward dates and
times can be when calculating with them

Have fun

Thanks for response which most of it works fine.

What I can't manage to do is the summing in the report footer. My total
works out at 790 total minutes and divide by 60 gives out 13.166667 which
isn't correct. I've tried =Format(TimeValue(Sum([TotalTIme])),"h:nn")
but I just get ERROR displayed. Is there another way to convert the
Report Footer Totals into Hours:Mins.

Many Thanks again

Don't format TotalTime in the query. I would convert this to the number
of minutes:

TotalTime: DateDiff("n",TimeValue([starttime]),TimeValue([finishtime]))
If starttime and finishtime are date/time fields with no date portion
then you could eliminate the TimeValue()s.

Summing TotalTime in your report footer will give you the total number of
minutes. Divide by 60 to get the number of hours.

You may also need to handle possible Null start or finish times.

--
Duane Hookom
MS Access MVP
--

I am having a problem totaling the total hours worked.

I have a query with a number of fields includes dates and times plus a
calculation that calculates the total time (spent on a job). I then use
this query on a report and place the Calculated field on the details
line. I then have a Grand Total in the Report Footer which totals all
the calculated times for each job.

Here's how the Table/Query are setup
The table which the queried fields refer to are all standard text fields
plus all time field properties set as :- Date/Time , Short Time, Input
Mask 00:00;0;_ which are fields 'StartTime' and 'FinishTime'.
The calculation field in the query is : TotalTime:
Format(TimeValue([finishtime])-TimeValue([starttime]),"h:nn").
The GrandTotal 'Control Source' in the Report Footer is set to
=Sum([TotalTime])

When I run the report, I get the message "The expression is typed
incorrectly, or it is too complex to be evaluated"

I have tried many different ways, even making a table out of the queried
results and summing on the fields but still get the same problems. Can
anyone help.

If more info is need, please let me know

Many thanks in Advance