Sum of DateDiff in a report

[jmprescott]

As the title says, I'm trying to find the sum of a field I created in a report, which is for the difference in 2 times (in seconds). Here's what I'm trying:

=Sum(DateDiff(" s",[ReviewStartTime],[ReviewStartTime]))

and it comes up as 0.

Is it because the field was created in the report and it's not an actual field on the table?

Is it possible to put a DateDiff as an actual field on the table and would that simplify things?

Thanks!

Jesse

[ChipR]

You should create a query based on the table and add a field where you put the formula. The query builder will put Expr1: in front of the formula, and you should change the name of the field to something sensible. Then just base the report on the query.

[jmprescott]

Oh wow, you're the best.

Now that makes me formatting the time more difficult. I have it in seconds in the query, and in the report, I want to convert it to say something like "1hr 12min 4sec". I know I could do this within the query, but I'd like to keep it in seconds there, so I can sum and average times within the report.

I think this makes sense. Any ideas?

[ChipR]

The Format function can sort-of do this.
Format (seconds / 86400, "hh:nn:ss")

[missinglinq]

Oh, come on people! Would you really expect

=Sum(DateDiff(" s",[ReviewStartTime],[ReviewStartTime]))

to return anything but zero? Both arguments are the same field!

Welcome to Bytes!

Linq ;0)>

[ChipR]

Hah, good one spotting that Linq :)
I was thinking of trying to get the Sum of a calcuated field in a report, but that would be a blank rather than zero wouldn't it? At least the effort of making the query isn't wasted.

[jmprescott]

Doh! Good catch, but that wasn't the problem, anyways, because I had tried it a few times in a few places a few different ways, and I know it was written correctly just about every time except the time I made the post :(

But yes, the query worked great!

Guh, I'm so clueless when it comes to access, but I'm learning. I'll do a little more research on the couple other questions I have and see if I can figure them out myself.

Thanks again!

[jmprescott]

Everything figured out, thanks for the help!

[missinglinq]

Glad we could help!

Linq ;0)>