Sum a time on a Rpt

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Rpt that I'm setting up so that I can see how I have spent my
time over a month. In the Query that is feeding the report I have set up the
following: Work Time: Format([CW_En_Time]-[CW_St_Time],"Short Time").
This gives me the total work time for each record. So I now have 10
Records with the time worked on each in a short Time Format 01:15 Etc. I
would like to total the time worked in a total time worked. I did a sum like
this =Sum([Work Time]) but I get the following error "Data type mismatch in
criteria expression" I do not know how to fix it any help would be great.

Thanks Keith
 
KAnoe said:
I have a Rpt that I'm setting up so that I can see how I have spent my
time over a month. In the Query that is feeding the report I have set up the
following: Work Time: Format([CW_En_Time]-[CW_St_Time],"Short Time").
This gives me the total work time for each record. So I now have 10
Records with the time worked on each in a short Time Format 01:15 Etc. I
would like to total the time worked in a total time worked. I did a sum like
this =Sum([Work Time]) but I get the following error "Data type mismatch in
criteria expression" I do not know how to fix it any help would be great.
Click to expand...


When you formatted the difference, you converted the result
to a string, which can not be summed.

Even if you fix that, there's more trouble ahead. The
difference of two points in time is a duration and durations
should be dealt with differently. This is normally done
using the DateDiff function to calculate the duration in the
smallest units of interest.

E.g. for minutes you would use
Work Time: DateDiff("n", [CW_St_Time], [CW_En_Time])

This can then be summed. Formatting is a separate issue
that is done in the report text box (probably in a group
footer) that displays the total by using an expression like:

=Sum([Work Time]) \ 60 & Format(Sum([Work Time]) Mod 60,
"\:00")
 
I will work it thats for your help!!

Keith

Marshall Barton said:
KAnoe said:
I have a Rpt that I'm setting up so that I can see how I have spent my
time over a month. In the Query that is feeding the report I have set up the
following: Work Time: Format([CW_En_Time]-[CW_St_Time],"Short Time").
This gives me the total work time for each record. So I now have 10
Records with the time worked on each in a short Time Format 01:15 Etc. I
would like to total the time worked in a total time worked. I did a sum like
this =Sum([Work Time]) but I get the following error "Data type mismatch in
criteria expression" I do not know how to fix it any help would be great.
Click to expand...


When you formatted the difference, you converted the result
to a string, which can not be summed.

Even if you fix that, there's more trouble ahead. The
difference of two points in time is a duration and durations
should be dealt with differently. This is normally done
using the DateDiff function to calculate the duration in the
smallest units of interest.

E.g. for minutes you would use
Work Time: DateDiff("n", [CW_St_Time], [CW_En_Time])

This can then be summed. Formatting is a separate issue
that is done in the report text box (probably in a group
footer) that displays the total by using an expression like:

=Sum([Work Time]) \ 60 & Format(Sum([Work Time]) Mod 60,
"\:00")
Click to expand...
 
hi,

i am trying that formula to sum all of the durations together in a report
but unfortunately it is not working out for me, can u tell me what im doing
wrong.

What i did is i created a text box at the bottom of the report and plugged
the following formula: =sum([total hours worked])

Marshall Barton said:
KAnoe said:
I have a Rpt that I'm setting up so that I can see how I have spent my
time over a month. In the Query that is feeding the report I have set up the
following: Work Time: Format([CW_En_Time]-[CW_St_Time],"Short Time").
This gives me the total work time for each record. So I now have 10
Records with the time worked on each in a short Time Format 01:15 Etc. I
would like to total the time worked in a total time worked. I did a sum like
this =Sum([Work Time]) but I get the following error "Data type mismatch in
criteria expression" I do not know how to fix it any help would be great.
Click to expand...


When you formatted the difference, you converted the result
to a string, which can not be summed.

Even if you fix that, there's more trouble ahead. The
difference of two points in time is a duration and durations
should be dealt with differently. This is normally done
using the DateDiff function to calculate the duration in the
smallest units of interest.

E.g. for minutes you would use
Work Time: DateDiff("n", [CW_St_Time], [CW_En_Time])

This can then be summed. Formatting is a separate issue
that is done in the report text box (probably in a group
footer) that displays the total by using an expression like:

=Sum([Work Time]) \ 60 & Format(Sum([Work Time]) Mod 60,
"\:00")
Click to expand...
 
HOW said:
i am trying that formula to sum all of the durations together in a report
but unfortunately it is not working out for me, can u tell me what im doing
wrong.

What i did is i created a text box at the bottom of the report and plugged
the following formula: =sum([total hours worked])
Click to expand...


If the durations are in hours that should work, unless
[total hours worked] is the name of a text box. The
aggregate functions (Count, Sum, etc) only operate on
**fields** in the record source table/query, they are
unaware of **controls** on a form/report.

If [total hours worked] is a value that's calculated in the
report, then we will need to know what the original fields
contain and what calculation was done.

If the calculation is a simple expression, then you can use
Sum on the expression. E.g.
Sum(DateDiff("h", starttime, endtime)
 
Back
Top