Calculating Total Time in a Report

Dave,

A quick question and this could be a case of me missing the obvious. I have
a report set up in a similar way to Kim's in that it based on a query.

In the query I have the expression TotalMinutes:
datediff("n",[TimeOn],[TimeOff]) which works perfectly calculating the total
number of minutes for each line in my report.

However I want to be able to "sum" the TotalMinutes in the report footer,
and no matter what I try I just can't get it to sum. In Report footer I have

=format(TotalMinutes\60, "#0:") & format(TotalMinutes Mod 60, "00")

Any help would be greatly appreciated.

Many thanks

JAMES

"Klatuu" wrote:

Sorry, I had not seen your query. You are doing the calculation in the query
and want to format it in the report. In that case, I think you would use:
= format(Expr1\60, "#0:") & format(Expr1 mod 60, "00")

A meaningful name other than Expr1 would be a good idea so the people who
come behind you and have to decypher your code wont call you bad names

The reason I posted my original repsonse was I did not know the caculation
was in the query. Normally, I do calculations in the report where I can. It
is faster to do it that way. In most cases, you would hardly know the
difference, but if there are a lot of caculations and a large recordset, it
can be a big difference. I once had a report that had about 80 calculations
on report. It ran almost an hour. I moved all the calculations from the
query to the report and it ran in about 90 seconds.
--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

Okay Dave, I'm feeling real stupid right about now...I can't get it to work.
Let me explain what I have:

I have a summary report, based on a query. This summary report shows the
Course ID, and the total time (duration) spent on that course. My query has:

Course
Time In
Time Out
Expr1ateDiff("n", [time in], [time out]

My report only shows the columns for Course, and Expr1. The Control Source
line for Expr1 says:

=Sum([Expr1])

If i change this to lngMinutesPassed = DateDiff("n", [time in], [time out]),
I get a syntax error:

Syntax error in query expression 'First([lngMinutesPassed=DateDiff("n",
[time in], [time out])])'

What am I doing wrong? This stuff is new to me, but I'm trying my best to
get it right. Please provide more explanation. Also, where do I put the
following code?

strDuration = format(lngMinutesdPassed\60, "#0:") & format[lngMinutesPassed
mod 60, "00")

"Klatuu" wrote:

Put it in the Control Source of the text box where you want it to display:

--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

Thank you. I put the =DateDiff("n",[time in],[time out]) in my query, and I
am able to run the report fine. But, I need clarification on where I put the
code to convert the number into hours:min format. Do I put it in the format
line in the report for that field, or somewhere else?

"Klatuu" wrote:

You are confusing time with duration. The Date/Time data type field contains
a specific point in time. not a duration. You derive duration by calculating
the number of units between two date/time fields. Units are some unit of
time (seconds, minutes, hours, etc). For this, you use the DateDiff funtion.
For example,
to get the number of minutes between [starttime] and [endtime] you would use:

=DateDiff("n", [starttime], [endtime]

That will return a long value of the number of minutes between the two
points in time. Assuming we don't care about seconds and only want to show
the number of minutes, this is sufficient; however, this is likely not the
case andleads us to the fun part. If we want hours and minutes, we have to
convert a number of minutes to hours and minutes. Here is a formula for
converting minutes to hours and minutes and displaying them in time format:

lngMinutesPassed = DateDiff("n", [starttime], [endtime]

strDuration = format(lngMinutesPassed\60, "#0:") & format(lngMinutesPassed
mod 60, "00")
--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

I have a query that displays a course ID and a calculated field
TotalTime:[endtime]-[starttime]

When I create a report based on this query, I can get all of the detail
information correctly, but it does not total the TotalTime column correctly.
The formula in the footer is
=sum([totaltime])

Can someone please help me with what I'm doing wrong?

James Frater wrote:
A quick question and this could be a case of me missing the obvious. I have
a report set up in a similar way to Kim's in that it based on a query.

In the query I have the expression TotalMinutes:
datediff("n",[TimeOn],[TimeOff]) which works perfectly calculating the total
number of minutes for each line in my report.

However I want to be able to "sum" the TotalMinutes in the report footer,
and no matter what I try I just can't get it to sum. In Report footer I have

=format(TotalMinutes\60, "#0:") & format(TotalMinutes Mod 60, "00")


You need to calculate the grand total minutes. I think this
will do it:

=Sum(TotalMinutes)\60 & Format(Sum(TotalMinutes) Mod 60,
"\:00")

--
Marsh
MVP [MS Access]

To get the total number of minutes you need to use an expression like:
Sum(TotalMinutes)

So your expression should be more like
=Format(Sum(TotalMinutes)\60, "#0:") & Format(Sum(TotalMinutes)Mod 60, "00")

It would help if you told us exactly what is wrong with your results. Blank,
wrong result, syntax error, etc. Without that information we are forced to
guess what is wrong and what the possible solution might be.

For instance, sometimes people try to do this in the page footer of the report
and not in the report footer. You can't do this directly in a page footer.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

James Frater wrote:
Dave,

A quick question and this could be a case of me missing the obvious. I have
a report set up in a similar way to Kim's in that it based on a query.

In the query I have the expression TotalMinutes:
datediff("n",[TimeOn],[TimeOff]) which works perfectly calculating the total
number of minutes for each line in my report.

However I want to be able to "sum" the TotalMinutes in the report footer,
and no matter what I try I just can't get it to sum. In Report footer I have

=format(TotalMinutes\60, "#0:") & format(TotalMinutes Mod 60, "00")

Any help would be greatly appreciated.

Many thanks

JAMES

"Klatuu" wrote:

Sorry, I had not seen your query. You are doing the calculation in the query
and want to format it in the report. In that case, I think you would use:
= format(Expr1\60, "#0:") & format(Expr1 mod 60, "00")

A meaningful name other than Expr1 would be a good idea so the people who
come behind you and have to decypher your code wont call you bad names

The reason I posted my original repsonse was I did not know the caculation
was in the query. Normally, I do calculations in the report where I can. It
is faster to do it that way. In most cases, you would hardly know the
difference, but if there are a lot of caculations and a large recordset, it
can be a big difference. I once had a report that had about 80 calculations
on report. It ran almost an hour. I moved all the calculations from the
query to the report and it ran in about 90 seconds.
--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

Okay Dave, I'm feeling real stupid right about now...I can't get it to work.
Let me explain what I have:

I have a summary report, based on a query. This summary report shows the
Course ID, and the total time (duration) spent on that course. My query has:

Course
Time In
Time Out
Expr1ateDiff("n", [time in], [time out]

My report only shows the columns for Course, and Expr1. The Control Source
line for Expr1 says:

=Sum([Expr1])

If i change this to lngMinutesPassed = DateDiff("n", [time in], [time out]),
I get a syntax error:

Syntax error in query expression 'First([lngMinutesPassed=DateDiff("n",
[time in], [time out])])'

What am I doing wrong? This stuff is new to me, but I'm trying my best to
get it right. Please provide more explanation. Also, where do I put the
following code?

strDuration = format(lngMinutesdPassed\60, "#0:") & format[lngMinutesPassed
mod 60, "00")

"Klatuu" wrote:

Put it in the Control Source of the text box where you want it to display:

--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

Thank you. I put the =DateDiff("n",[time in],[time out]) in my query, and I
am able to run the report fine. But, I need clarification on where I put the
code to convert the number into hours:min format. Do I put it in the format
line in the report for that field, or somewhere else?

"Klatuu" wrote:

You are confusing time with duration. The Date/Time data type field contains
a specific point in time. not a duration. You derive duration by calculating
the number of units between two date/time fields. Units are some unit of
time (seconds, minutes, hours, etc). For this, you use the DateDiff funtion.
For example,
to get the number of minutes between [starttime] and [endtime] you would use:

=DateDiff("n", [starttime], [endtime]

That will return a long value of the number of minutes between the two
points in time. Assuming we don't care about seconds and only want to show
the number of minutes, this is sufficient; however, this is likely not the
case andleads us to the fun part. If we want hours and minutes, we have to
convert a number of minutes to hours and minutes. Here is a formula for
converting minutes to hours and minutes and displaying them in time format:

lngMinutesPassed = DateDiff("n", [starttime], [endtime]

strDuration = format(lngMinutesPassed\60, "#0:") & format(lngMinutesPassed
mod 60, "00")
--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

I have a query that displays a course ID and a calculated field
TotalTime:[endtime]-[starttime]

When I create a report based on this query, I can get all of the detail
information correctly, but it does not total the TotalTime column correctly.
The formula in the footer is
=sum([totaltime])

Can someone please help me with what I'm doing wrong?

Marshall and John,

Thank you for such a speedy response.

The expression
=Format(Sum(TotalMinutes)\60, "#0:") & Format(Sum(TotalMinutes)Mod 60, "00")
is working perfectly.

Apologies for the lack of information, I'll make sure I add more in the
future.

Many, many thanks

JAMES


"John Spencer (MVP)" wrote:

To get the total number of minutes you need to use an expression like:
Sum(TotalMinutes)

So your expression should be more like
=Format(Sum(TotalMinutes)\60, "#0:") & Format(Sum(TotalMinutes)Mod 60, "00")

It would help if you told us exactly what is wrong with your results. Blank,
wrong result, syntax error, etc. Without that information we are forced to
guess what is wrong and what the possible solution might be.

For instance, sometimes people try to do this in the page footer of the report
and not in the report footer. You can't do this directly in a page footer.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

James Frater wrote:
Dave,

A quick question and this could be a case of me missing the obvious. I have
a report set up in a similar way to Kim's in that it based on a query.

In the query I have the expression TotalMinutes:
datediff("n",[TimeOn],[TimeOff]) which works perfectly calculating the total
number of minutes for each line in my report.

However I want to be able to "sum" the TotalMinutes in the report footer,
and no matter what I try I just can't get it to sum. In Report footer I have

=format(TotalMinutes\60, "#0:") & format(TotalMinutes Mod 60, "00")

Any help would be greatly appreciated.

Many thanks

JAMES

"Klatuu" wrote:

Sorry, I had not seen your query. You are doing the calculation in the query
and want to format it in the report. In that case, I think you would use:
= format(Expr1\60, "#0:") & format(Expr1 mod 60, "00")

A meaningful name other than Expr1 would be a good idea so the people who
come behind you and have to decypher your code wont call you bad names

The reason I posted my original repsonse was I did not know the caculation
was in the query. Normally, I do calculations in the report where I can. It
is faster to do it that way. In most cases, you would hardly know the
difference, but if there are a lot of caculations and a large recordset, it
can be a big difference. I once had a report that had about 80 calculations
on report. It ran almost an hour. I moved all the calculations from the
query to the report and it ran in about 90 seconds.
--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

Okay Dave, I'm feeling real stupid right about now...I can't get it to work.
Let me explain what I have:

I have a summary report, based on a query. This summary report shows the
Course ID, and the total time (duration) spent on that course. My query has:

Course
Time In
Time Out
Expr1ateDiff("n", [time in], [time out]

My report only shows the columns for Course, and Expr1. The Control Source
line for Expr1 says:

=Sum([Expr1])

If i change this to lngMinutesPassed = DateDiff("n", [time in], [time out]),
I get a syntax error:

Syntax error in query expression 'First([lngMinutesPassed=DateDiff("n",
[time in], [time out])])'

What am I doing wrong? This stuff is new to me, but I'm trying my best to
get it right. Please provide more explanation. Also, where do I put the
following code?

strDuration = format(lngMinutesdPassed\60, "#0:") & format[lngMinutesPassed
mod 60, "00")

"Klatuu" wrote:

Put it in the Control Source of the text box where you want it to display:

--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

Thank you. I put the =DateDiff("n",[time in],[time out]) in my query, and I
am able to run the report fine. But, I need clarification on where I put the
code to convert the number into hours:min format. Do I put it in the format
line in the report for that field, or somewhere else?

"Klatuu" wrote:

You are confusing time with duration. The Date/Time data type field contains
a specific point in time. not a duration. You derive duration by calculating
the number of units between two date/time fields. Units are some unit of
time (seconds, minutes, hours, etc). For this, you use the DateDiff funtion.
For example,
to get the number of minutes between [starttime] and [endtime] you would use:

=DateDiff("n", [starttime], [endtime]

That will return a long value of the number of minutes between the two
points in time. Assuming we don't care about seconds and only want to show
the number of minutes, this is sufficient; however, this is likely not the
case andleads us to the fun part. If we want hours and minutes, we have to
convert a number of minutes to hours and minutes. Here is a formula for
converting minutes to hours and minutes and displaying them in time format:

lngMinutesPassed = DateDiff("n", [starttime], [endtime]

strDuration = format(lngMinutesPassed\60, "#0:") & format(lngMinutesPassed
mod 60, "00")
--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

I have a query that displays a course ID and a calculated field
TotalTime:[endtime]-[starttime]

When I create a report based on this query, I can get all of the detail
information correctly, but it does not total the TotalTime column correctly.
The formula in the footer is
=sum([totaltime])

Can someone please help me with what I'm doing wrong?

-=Marshall + John,

After merrily do a few laps around the office, the building, and buying a
victory round of teas from the expensive cafe. I've come across a problem.

When the TimeOn and TimeOff cross over midnight TotalMinute returns a
negative result.

For example:
TimeOn = 23:50
TimeOff = 04:00
TotalMinutes = -1190 (I would expect this to return 290minutes)

So when summed with the other TotalMinute results using the expression from
your last post I get a reading of , -10:-36

Any thoughts?

Much Obliged

JAMES

"John Spencer (MVP)" wrote:

To get the total number of minutes you need to use an expression like:
Sum(TotalMinutes)

So your expression should be more like
=Format(Sum(TotalMinutes)\60, "#0:") & Format(Sum(TotalMinutes)Mod 60, "00")

It would help if you told us exactly what is wrong with your results. Blank,
wrong result, syntax error, etc. Without that information we are forced to
guess what is wrong and what the possible solution might be.

For instance, sometimes people try to do this in the page footer of the report
and not in the report footer. You can't do this directly in a page footer.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

James Frater wrote:
Dave,

A quick question and this could be a case of me missing the obvious. I have
a report set up in a similar way to Kim's in that it based on a query.

In the query I have the expression TotalMinutes:
datediff("n",[TimeOn],[TimeOff]) which works perfectly calculating the total
number of minutes for each line in my report.

However I want to be able to "sum" the TotalMinutes in the report footer,
and no matter what I try I just can't get it to sum. In Report footer I have

=format(TotalMinutes\60, "#0:") & format(TotalMinutes Mod 60, "00")

Any help would be greatly appreciated.

Many thanks

JAMES

"Klatuu" wrote:

Sorry, I had not seen your query. You are doing the calculation in the query
and want to format it in the report. In that case, I think you would use:
= format(Expr1\60, "#0:") & format(Expr1 mod 60, "00")

A meaningful name other than Expr1 would be a good idea so the people who
come behind you and have to decypher your code wont call you bad names

The reason I posted my original repsonse was I did not know the caculation
was in the query. Normally, I do calculations in the report where I can. It
is faster to do it that way. In most cases, you would hardly know the
difference, but if there are a lot of caculations and a large recordset, it
can be a big difference. I once had a report that had about 80 calculations
on report. It ran almost an hour. I moved all the calculations from the
query to the report and it ran in about 90 seconds.
--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

Okay Dave, I'm feeling real stupid right about now...I can't get it to work.
Let me explain what I have:

I have a summary report, based on a query. This summary report shows the
Course ID, and the total time (duration) spent on that course. My query has:

Course
Time In
Time Out
Expr1ateDiff("n", [time in], [time out]

My report only shows the columns for Course, and Expr1. The Control Source
line for Expr1 says:

=Sum([Expr1])

If i change this to lngMinutesPassed = DateDiff("n", [time in], [time out]),
I get a syntax error:

Syntax error in query expression 'First([lngMinutesPassed=DateDiff("n",
[time in], [time out])])'

What am I doing wrong? This stuff is new to me, but I'm trying my best to
get it right. Please provide more explanation. Also, where do I put the
following code?

strDuration = format(lngMinutesdPassed\60, "#0:") & format[lngMinutesPassed
mod 60, "00")

"Klatuu" wrote:

Put it in the Control Source of the text box where you want it to display:

--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

Thank you. I put the =DateDiff("n",[time in],[time out]) in my query, and I
am able to run the report fine. But, I need clarification on where I put the
code to convert the number into hours:min format. Do I put it in the format
line in the report for that field, or somewhere else?

"Klatuu" wrote:

You are confusing time with duration. The Date/Time data type field contains
a specific point in time. not a duration. You derive duration by calculating
the number of units between two date/time fields. Units are some unit of
time (seconds, minutes, hours, etc). For this, you use the DateDiff funtion.
For example,
to get the number of minutes between [starttime] and [endtime] you would use:

=DateDiff("n", [starttime], [endtime]

That will return a long value of the number of minutes between the two
points in time. Assuming we don't care about seconds and only want to show
the number of minutes, this is sufficient; however, this is likely not the
case andleads us to the fun part. If we want hours and minutes, we have to
convert a number of minutes to hours and minutes. Here is a formula for
converting minutes to hours and minutes and displaying them in time format:

lngMinutesPassed = DateDiff("n", [starttime], [endtime]

strDuration = format(lngMinutesPassed\60, "#0:") & format(lngMinutesPassed
mod 60, "00")
--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

I have a query that displays a course ID and a calculated field
TotalTime:[endtime]-[starttime]

When I create a report based on this query, I can get all of the detail
information correctly, but it does not total the TotalTime column correctly.
The formula in the footer is
=sum([totaltime])

Can someone please help me with what I'm doing wrong?

James Frater wrote:

-=Marshall + John,

After merrily do a few laps around the office, the building, and buying a
victory round of teas from the expensive cafe. I've come across a problem.

When the TimeOn and TimeOff cross over midnight TotalMinute returns a
negative result.

For example:
TimeOn = 23:50
TimeOff = 04:00
TotalMinutes = -1190 (I would expect this to return 290minutes)

So when summed with the other TotalMinute results using the expression from
your last post I get a reading of , -10:-36


Your TimeOn and TimeOff fields need to include the date
part.

How are these fields being set?

--
Marsh
MVP [MS Access]

Marshall,

TimeOn and TimeOff were date/time fields set to "short time" and were
seperate fields to DateOn and DateOff. However I've now added two fields
called CombinedOn and CombinedOff which I've set to General Date dd/mm/yyyy
hh:mm:ss and using a update query I've combined the date and time.

I've coded the Update Query to run on the Click() command to run the report,
is that the right place to put it?

It seems to be working properly and I've gone through as many through
midnight reports as possible. But even so I'll keep my celebrations to a
minimum this time!!!!!

Many thanks as always.

JAMES

"Marshall Barton" wrote:

James Frater wrote:

-=Marshall + John,

After merrily do a few laps around the office, the building, and buying a
victory round of teas from the expensive cafe. I've come across a problem.

When the TimeOn and TimeOff cross over midnight TotalMinute returns a
negative result.

For example:
TimeOn = 23:50
TimeOff = 04:00
TotalMinutes = -1190 (I would expect this to return 290minutes)

So when summed with the other TotalMinute results using the expression from
your last post I get a reading of , -10:-36


Your TimeOn and TimeOff fields need to include the date
part.

How are these fields being set?

--
Marsh
MVP [MS Access]

James Frater wrote:
TimeOn and TimeOff were date/time fields set to "short time" and were
seperate fields to DateOn and DateOff. However I've now added two fields
called CombinedOn and CombinedOff which I've set to General Date dd/mm/yyyy
hh:mm:ss and using a update query I've combined the date and time.

I've coded the Update Query to run on the Click() command to run the report,
is that the right place to put it?

It seems to be working properly and I've gone through as many through
midnight reports as possible. But even so I'll keep my celebrations to a
minimum this time!!!!!


That should work, but is is a very heavy handed way of doing
things.

Better to combine the date and time parts in the report's
record source query. Taking advantage of the way dates and
times are stored, the combined value can be calculated in a
query by adding a calculated field to the query:

CombinedOn: DateOn + TimeOn

Or, skip the combined fields and just use the same thing in
your DateDiff:

TotalMinutes: datediff("n", DateOn + TimeOn, DateOff +
TimeOff)

Even better than all that would be to remove the separate
fields and just have the combined fields in the table. You
probably have separate text boxes for the dates and times in
the data entry form (which I would think is a pain), but if
you really want users to enter them separately, they can be
combined in the form's BeforeUpdate event.

--
Marsh
MVP [MS Access]

Marshall,

Thanks for that. I've gone with your second option TotalMinutes:
datediff("n", DateOn + TimeOn, DateOff + TimeOff), it removes the need for
update query and makes things run a lot quicker.

I'm afraid I've inherited the database so there are lots of quirks where
nothing short of starting again would resolve all of the issues! But as I'm
new to the company I'm not going to rock the boat just yet!

Thanks for all your help, it is greatly appreciated.

JAMES

"Marshall Barton" wrote:

James Frater wrote:
TimeOn and TimeOff were date/time fields set to "short time" and were
seperate fields to DateOn and DateOff. However I've now added two fields
called CombinedOn and CombinedOff which I've set to General Date dd/mm/yyyy
hh:mm:ss and using a update query I've combined the date and time.

I've coded the Update Query to run on the Click() command to run the report,
is that the right place to put it?

It seems to be working properly and I've gone through as many through
midnight reports as possible. But even so I'll keep my celebrations to a
minimum this time!!!!!


That should work, but is is a very heavy handed way of doing
things.

Better to combine the date and time parts in the report's
record source query. Taking advantage of the way dates and
times are stored, the combined value can be calculated in a
query by adding a calculated field to the query:

CombinedOn: DateOn + TimeOn

Or, skip the combined fields and just use the same thing in
your DateDiff:

TotalMinutes: datediff("n", DateOn + TimeOn, DateOff +
TimeOff)

Even better than all that would be to remove the separate
fields and just have the combined fields in the table. You
probably have separate text boxes for the dates and times in
the data entry form (which I would think is a pain), but if
you really want users to enter them separately, they can be
combined in the form's BeforeUpdate event.

--
Marsh
MVP [MS Access]