Count or Sum if field 1 = yes and field 2 is <= "01:00" 1

[kimmer7389]

I have an Access Report and I am trying to display a total if the Count or Sum (Have not gotten either to work) of Lav Values completed = yes (1) AND Total Hours are less than are greater than "01:00"

I have written an expression:
=Abs(Sum(IIf([Critical_Lab_Value_Report_Completed],1,[Total_Hours]<="01:00")))

I can get the Yes part to work but not the second part where I need Total Hours field to be <= "01:00"

What am I doing wrong.

Please Help.
Kim

 

[SkipVought]

Hi,

Date/Time values are NUMBERS not TEXT. Your TEXT myust be CONVERTED using the ## conversion delimiters...

=Abs(Sum(IIf([Critical_Lab_Value_Report_Completed],1,[Total_Hours]<=#01:00#)))

or

=Abs(Sum(IIf([Critical_Lab_Value_Report_Completed],1,[Total_Hours]<=TimeSerial(1,0,0))))

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kimmer7389]

Ok so I decided instead of using time and hours, because that wasn't working well, I would use minutes.

So now I am trying
=Abs(Sum(IIf([Critical_Lab_Value_Report_Completed],1,[Total_Minutes]<=60)))

It dosen't appear that the AND part is working. I need Critical_Lab_Value_Report_Completed Yes AND Total Minutes to be Less than or Equal to 60.

 

[SkipVought]

It dosen't appear that the AND part is working.

=Abs(Sum(IIf([Critical_Lab_Value_Report_Completed],1,[Total_Minutes]<=60)))

?????

Maybe???

=Abs(Sum(IIf([Critical_Lab_Value_Report_Completed] [b]AND[/b] [Total_Minutes]<=60,1,0)))

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kimmer7389]

=Abs(Sum(IIf([Critical_Lab_Value_Report_Completed] AND [Total_Minutes]<=60,1,0)))

It worked!

Thank you!

 

[SkipVought]

You really do not need the ABS fucntion as you have no negative values.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kimmer7389]

That is true. I was using ABS before because I kept getting a negative number but that was because I had the wrong formula.

So now my expression is:

=Sum(IIf([Critical_Lab_Value_Report_Completed] And [Total_Minutes]<=60,1,0))

It works wonderfully.