Converting total min. in a report from a summary subquery to hrs. and min.

[Lee B via AccessMonster.com]

I have a report based a subquery that is based upon a query. The subquery
produces a sum of time in minutes for each field name and these are grouped
and sorted alphabetically. The result is a group total (SumOfTimeWorkedC)
expressed in total minutes for each field name.

In the Detail section of the report, I need to express (SumOfTimeWorkedC),
which is in minutes, in total hours and minutes instead of minutes only. As
well, in the Report footer I have two text boxes. One that totals the minutes
Sum([SumofTimeWorkedC]) and one that takes the sum of minutes and expresses
it in hours and minutes using this formula: =Sum([SumOfTimeWorkedC])\60 &
Format([SumOfTimeWorkedC] Mod 60,"\:00"). This formula works well and it
gives me a number for total hours and minutes. My problem is coming up with a
working formula that will take the total grouped minutes in the Detail
section of the form and express them in hours and minutes. Any help will be
greatly appreciated!
Thanks,
Lee

 

[Guest]

Lee,

Assuming the minutes are stored as a number and not a date time:
Format ([total number of minutes]/1440,"hh:mm")

The 1440 is the number of minutes in a day and converts your count / total
into a time format recognised by Access. (The integer part of a date/time is
the # days since 31 dec 1899 and the decimal part is the time)


HTH

Terry

 

[Lee B via AccessMonster.com]

Terry, Thankyou for the info. I used the expression you gave me in my summary
query (which is based on a query). I entered it like: HHmm: Format(
[SumOfTimeWorkedC]/1440,"hh:mm") as you suggested. The problem is it won't
work because It's too complex and I get the message that I cannot use a
subquery to make the calculation. The information is stored as a number and
if I enter the format into the original query, where it works fine, in "Group
By", my summary query cannot calculate a total using "Sum". Any more ideas?
Lee

Lee,

Assuming the minutes are stored as a number and not a date time:
Format ([total number of minutes]/1440,"hh:mm")

The 1440 is the number of minutes in a day and converts your count / total
into a time format recognised by Access. (The integer part of a date/time is
the # days since 31 dec 1899 and the decimal part is the time)

HTH

Terry

I have a report based a subquery that is based upon a query. The subquery
produces a sum of time in minutes for each field name and these are grouped

[quoted text clipped - 13 lines]

Thanks,
Lee

 

[Guest]

Lee,

I would be more inclined to format the numbers into times in the report
itself. Where you want to display the hours/minutes add a control and set the
control source to Format([SumOfTimeWorkedC]/1440,"hh:mm").

I get a bit lost when the "too complex" messages start appearing (anyone got
an explanation ... ?).

HTH

Terry

Terry, Thankyou for the info. I used the expression you gave me in my summary
query (which is based on a query). I entered it like: HHmm: Format(
[SumOfTimeWorkedC]/1440,"hh:mm") as you suggested. The problem is it won't
work because It's too complex and I get the message that I cannot use a
subquery to make the calculation. The information is stored as a number and
if I enter the format into the original query, where it works fine, in "Group
By", my summary query cannot calculate a total using "Sum". Any more ideas?
Lee

Lee,

Assuming the minutes are stored as a number and not a date time:
Format ([total number of minutes]/1440,"hh:mm")

The 1440 is the number of minutes in a day and converts your count / total
into a time format recognised by Access. (The integer part of a date/time is
the # days since 31 dec 1899 and the decimal part is the time)

HTH

Terry

I have a report based a subquery that is based upon a query. The subquery
produces a sum of time in minutes for each field name and these are grouped

[quoted text clipped - 13 lines]

Thanks,
Lee

 

[Lee B via AccessMonster.com]

Terry, just thought I would let you know. The following entered into the text
box control source did the trick. It works beautifully!
=CStr(Int([SumOfTimeWorkedM]\60)) & ":" & Format$([SumOfTimeWorkedM] Mod 60,
"00")
Thanks for your suggestions.
Lee

Lee,

I would be more inclined to format the numbers into times in the report
itself. Where you want to display the hours/minutes add a control and set the
control source to Format([SumOfTimeWorkedC]/1440,"hh:mm").

I get a bit lost when the "too complex" messages start appearing (anyone got
an explanation ... ?).

HTH

Terry

Terry, Thankyou for the info. I used the expression you gave me in my summary
query (which is based on a query). I entered it like: HHmm: Format(

[quoted text clipped - 23 lines]