another SUM problem

[pikoy]

I need help on a sum expression problem. I did a seach and noticed that there is another post on the issue but they dont resemble my own. Hoping anyone can help me out.

The problem is that, on a report, i have an field that calculates the difference between inspect_start and inspect_end both are (date and time ). There are multiple sectors in a build that will have this inspect start and end. My problem is how to total the output of these fields (sector unbound field ) per build.

[Build] [Total of Unbound]
[Sector] [Unbound]

The sector unbound is calculated by the code below:

=DateDiff("d",[inspect_Start],[inspect_End]) & "d, " & DateDiff("h",[inspect_Start],[inspect_End]) Mod 24 & "h, " & DateDiff("n",[inspect_Start],[inspect_End]) Mod 60 & "m, " & DateDiff("s",[inspect_Start],[inspect_End]) Mod 60 & "s"

any help is appreciated

Thank you

 

[vbaInet]

Put the calculation in the record source of the report and you will be able to perform a Sum() on that alias field.

The aggregate functions like Sum() (for example) only work on records that are in the record source of the object.

 

[pikoy]

Put the calculation in the record source of the report and you will be able to perform a Sum() on that alias field.

The aggregate functions like Sum() (for example) only work on records that are in the record source of the object.

yup... but its giving me an error.

 

[pikoy]

redid the code but still an error on the field.

=Sum(IIf(Len(Trim(DateDiff("d",[inspect_Start],[inspect_End]) & "d, " & DateDiff("h",[inspect_Start],[inspect_End]) Mod 24 & "h, " & DateDiff("n",[inspect_Start],[inspect_End]) Mod 60 & "m, " & DateDiff("s",[inspect_Start],[inspect_End]) Mod 60 & "s"))>0,1,0))

 

[vbaInet]

yup... but its giving me an error.

You didn't understand what I said. Move the calculation to the query completely, so in the query the new column will be something like:

Expr1: DateDiff(...)

Then sum on Expr1 in your report:

Sum([Expr1])

Obviously call Expr1 something meaningful.

 

[pikoy]

You didn't understand what I said. Move the calculation to the query completely, so in the query the new column will be something like:

Expr1: DateDiff(...)

Then sum on Expr1 in your report:

Sum([Expr1])

Obviously call Expr1 something meaningful.

Already did that: On Query: TotalInspectTime: datediff(..code..)
then on the report field =Sum([TotalInspectTime]) still an error.

 

[vbaInet]

Then your problem is in the DateDiff(). Did you open the query in datasheet view to see if you get the right results or #Error?

 

[pikoy]

Then your problem is in the DateDiff(). Did you open the query in datasheet view to see if you get the right results or #Error?

yup. the datediff gives out what i want (days, hours, min, sec) no problems on that...

its when i sum it as an aggregate that it gives out the error. for the heck of me, i dont know where or what im doing wrong.

 

[vbaInet]

Let me see your db.

 

[pikoy]

Let me see your db.

Found the issue.... placed a sample data to test it and found that i was calling something that should not be called in the expression.

imgetting a mismatch

=SUM([TotalInspectTime])

and if i do #" and "#" i get an invalid date value... that should be # right? since its based on dates.

 

[vbaInet]

Your field is returning Text. You cannot Sum() Text.

 

[pikoy]

Your field is returning Text. You cannot Sum() Text.

So, redid the whole thing and now i get seconds on query.

so when I do =Sum([TotalInspectTime]) - i get total in seconds... how do i get this back to dd:hh:nn:ss format.

tried this but it does not work. Do you know of any other way?

=Sum([TotalTestTime]\86400,"dd:hh:nn:ss")

Thanks again.

 

[vbaInet]

Perhaps this:

fix(Sum([TotalTestTime]) / 86400) & ":" & format(Sum([TotalTestTime]) / 86400, "hh:nn:ss")

 

[pikoy]

Perhaps this:

fix(Sum([TotalTestTime]) / 86400) & ":" & format(Sum([TotalTestTime]) / 86400, "hh:nn:ss")

Thanks.... but its giving the wrong total.

 

[pikoy]

Thanks.... but its giving the wrong total.

vbaInet,

You were right all along... the datediff was the problem. had to revise everything. Its all OK now. Thanks for the great help.

 

[vbaInet]

Happy that it's resolved.