[X]My Assistant

[brastedhouse]

Good afternoon,

in query:
Hours: DateDiff("n",[drillStartTime],[drillEndTime])
to calculate the elapsed time of an event

in text box on report
=[Hours]\60 & Format([Hours] Mod 60,"\:00")
to get [Hours] to display as hours and minutes

This works like a charm. Now I would like to limit the calculation to the value of another field. I have a check box (drillSubjectOSHA)and I want to limit the Hours in a text box on the report to the hours when the check box is true. I think I need a where clause or something like it, but I have no idea how to do this.

Any help would be appreciated.

Best, Scott

[Doug Steele]

If I'm understanding your question correctly, you don't need to change the calculation for Hours, you need to set a WHERE clause for your query so it only returns those rows where the check box is true.

[projecttoday]

If you want to include all the rows in the query but only include in the calculation those with the box checked then you need an Iif.

[brastedhouse]

Hi, yes and yes. Calculation is good, just need to delimit it. and yes I am hoping for an IIf in a text box.I already have a query returning the elapsed time. An IIf something like:

CODE

=Sum(IIf([drillSubjectOSHA],1,0))

(drillSubjectOSHA is a yes/no field)

which I am using to get the total of a count if that field is true. But I cannot work out how to manipulate this example into what I need.

In the end I want to total the sum of [Hours] delimited by the yes/no field. I was thinking of something like this,

CODE

=Sum(IIF([drillSubjectOSHA]) = 1, ([Hours]\60 & Format([Hours] Mod 60,"\:00")), 0)

but it gives me am error saying wrong number of arguments.

Thanks, Scott
This post has been edited by brastedhouse: Oct 20 2019, 03:01 PM

[arnelgp]

maybe something like:

=Sum(IIf([drillSubjectOSHA] And [drillSubjectOSHA], [Hours], 0))

[brastedhouse]

That gives me the total of both yes and no. I have a small table for an experiment and it has several bogus records. There is a record for this member with OSHA and a record of non-OSHA. The result should be 2 total drills for the member and 2 total hours and 1 OHSA hour. I get 2 OSHA hours. hmmm.

[brastedhouse]

OK, my mistake. It is right. VERY Sorry! THANK YOU! Can you tell me how this works?

[brastedhouse]

Also, when I do this:

=Sum([Hours]) I get the correct result (180) in minutes.


When I do this:

=Sum([Hours]\60 & Format([Hours] Mod 60,"\:00"))

I get Property Not Found instead of 3 (Hours)

[Doug Steele]

Your formatted converts the value to a text string. You can't sum text strings.

Do the summation, then do the formatting on the sum.

[brastedhouse]

I don't understand how to do the summation, then do the formatting on the sum.

EDIT - AFTER LOTS OF STARING AND SCRATCHING . . . AH HAH!

Now I get it. I left the original with the minutes and made it not visible and covered it with another visible text box that had the formatting in it. Now I have the right duration and the right formatting. Is this what you were referring to?

Best, Scott
This post has been edited by brastedhouse: Oct 20 2019, 09:50 PM

[Doug Steele]

That was probably the easiest approach.

Another would have been =Sum([Hours])\60 & Format(Sum([Hours]) Mod 60,"\:00"), but that would require more calculations.

Glad you got it to work.