Calculating Total Time in a Report

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?

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?

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?

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?

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?

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?

That worked!!!! That info about moving the calculation to the report is very
useful. I will do that. I had no idea it made a difference.

As for renaming that Expr1 to something more meaningful, I keep trying to do
that, but then my totals just sequence from 1 to N. I have tried changing it
in the query grid by putting TotalTime: in front of the calculation, but that
doesn't work, and I have even tried changing the caption in the properties
sheet. If you have any idea why my numbers change when I change the caption,
please let me know. i've never had problems like that before.

Thanks so much for your help!! you are a lifesaver!

"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?

It is only a problem when using Jet. Jet is not a client server app. It is
a file server app. What that means is all the processing occurs on the
workstation in a file server app. In a Client Server app, the client
(workstation) requests the data via sql and the Server returns only the
requested data. The file server app returns the entire table or tables and
the workstation has to select and arrange the data. If you use calculations
in the query, it has to go back and forth to the server to get and format the
data. If you do the calculations is the report, it already has the data, so
it doesn't have to go back to the servier again. This is also an impact on
network response time. If your coworkers find out it is you slowing down
their work, the will come to your cubicle and beat you senseless
--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

That worked!!!! That info about moving the calculation to the report is very
useful. I will do that. I had no idea it made a difference.

As for renaming that Expr1 to something more meaningful, I keep trying to do
that, but then my totals just sequence from 1 to N. I have tried changing it
in the query grid by putting TotalTime: in front of the calculation, but that
doesn't work, and I have even tried changing the caption in the properties
sheet. If you have any idea why my numbers change when I change the caption,
please let me know. i've never had problems like that before.

Thanks so much for your help!! you are a lifesaver!

"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?

All the more reason to move it to the report . I'll do that right away!
Thanks again for your help, you're wonderful!

"Klatuu" wrote:

It is only a problem when using Jet. Jet is not a client server app. It is
a file server app. What that means is all the processing occurs on the
workstation in a file server app. In a Client Server app, the client
(workstation) requests the data via sql and the Server returns only the
requested data. The file server app returns the entire table or tables and
the workstation has to select and arrange the data. If you use calculations
in the query, it has to go back and forth to the server to get and format the
data. If you do the calculations is the report, it already has the data, so
it doesn't have to go back to the servier again. This is also an impact on
network response time. If your coworkers find out it is you slowing down
their work, the will come to your cubicle and beat you senseless
--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

That worked!!!! That info about moving the calculation to the report is very
useful. I will do that. I had no idea it made a difference.

As for renaming that Expr1 to something more meaningful, I keep trying to do
that, but then my totals just sequence from 1 to N. I have tried changing it
in the query grid by putting TotalTime: in front of the calculation, but that
doesn't work, and I have even tried changing the caption in the properties
sheet. If you have any idea why my numbers change when I change the caption,
please let me know. i've never had problems like that before.

Thanks so much for your help!! you are a lifesaver!

"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?

Glad I could help.
--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

All the more reason to move it to the report . I'll do that right away!
Thanks again for your help, you're wonderful!

"Klatuu" wrote:

It is only a problem when using Jet. Jet is not a client server app. It is
a file server app. What that means is all the processing occurs on the
workstation in a file server app. In a Client Server app, the client
(workstation) requests the data via sql and the Server returns only the
requested data. The file server app returns the entire table or tables and
the workstation has to select and arrange the data. If you use calculations
in the query, it has to go back and forth to the server to get and format the
data. If you do the calculations is the report, it already has the data, so
it doesn't have to go back to the servier again. This is also an impact on
network response time. If your coworkers find out it is you slowing down
their work, the will come to your cubicle and beat you senseless
--
Dave Hargis, Microsoft Access MVP


"KimPotter" wrote:

That worked!!!! That info about moving the calculation to the report is very
useful. I will do that. I had no idea it made a difference.

As for renaming that Expr1 to something more meaningful, I keep trying to do
that, but then my totals just sequence from 1 to N. I have tried changing it
in the query grid by putting TotalTime: in front of the calculation, but that
doesn't work, and I have even tried changing the caption in the properties
sheet. If you have any idea why my numbers change when I change the caption,
please let me know. i've never had problems like that before.

Thanks so much for your help!! you are a lifesaver!

"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?