How to Count field values with criteria in Report

[Jerry Maiapu]

In my tabular (format) report I would like to count only the fields with “AOT” in a column Overtime. How do I do that in the report? I know this is possible in query but for certain reasons I want to do the count on the report.

On the Report footer, I did the following in a text box but did not get the expected results.

 =count([Overtime]=”AOT”)

Problem Background:
I have a column overtime with No and “AOT”

Example Display:

Overtime
No
No
AOT
AOT
NO
AOT

I wish to count only the AOT, which would obviously be 3.How do I accomplish this?

Trying this

=count([Overtime]=”AOT”)

gives me the total count ignoring the condition
Trying these

=Count(where [Overtime]=”AOT”)

 =Count([Overtime]Like”AOT”)

gives error

I give up.
I thought this would be somehow possible however I could not get it through.

Can someone show me a better way of handling this?



Thank you in advance

Jerry

[nico5038]

Did you check the DCount() function ?
It will do the job, but it's making your query/report a lot slower.
When too slow then I advise to use a groupby query and link the field in a subreport.

Nic;o)

[NeoPa]

Jerry,

It seems your problem is in counting boolean values. Count() will add 1 any time it finds a value that is not Null. True/False is immaterial.

Try instead :

=Count(IIf([Overtime]='AOT',True,Null))

Please also remember in future only to copy/paste code in to a post. Typing it out free-hand causes errors (a number in this case) that can waste time.

[Jerry Maiapu]

Thanks Nico and NeoPa. NeoPa's solution worked perfectly.

[Jerry Maiapu]

Hi Nico and NeoPa,

I just bumped into a very similar problem and I hope you could also help. I 'm sorry if this is disturbing but I need to get this thing out of the way. Its on the same report.

Just like the count on the OVERTIME column I would like to DO A SIMILAR count ON a DATE column. I just want to count the dates excluding Sunday and Saturday.
I tried these using NeoPa's concept:

=Count(IIf(Weekday([DateWorked])=1 Or 7,True,Null))

=Count(IIf(Weekday([DateWorked])="Sunday" Or "Saturday",True,Null))

Both gives the total count even though there a couple of Sundays and Saturdays in the column list.

How could I possibly achieve this?

Thanks.

Jerry

[NeoPa]

Try :

=Count(IIf(Weekday([DateWorked]) In(1,7),True,Null))

If that doesn't work then you need to specify two full comparisons, rather than trying to do an OR list for a single comparison.

=Count(IIf((Weekday([DateWorked])=1) OR (Weekday([DateWorked])=7),True,Null))

I suspect, as this actually resolves eventually to SQL code, that the In() approach will work fine.

[NeoPa]

By the way, an alternative way to count your required values is to use Sum(). In this case instead of providing Null & non-Null values, you'd use 1 & 0. This would work equally well, and be possibly more intuitive, although the word Count may help a reader to understand what the idea is. It's just a thought. Whichever you prefer really. Sometimes the use of Null in a counting procedure can mean very little to a reader.

[Jerry Maiapu]

Thanks for the solution:
Combining your suggestions of Sum()Post#7 and solution Post#6 I did this

=Sum(IIf((Weekday([DateWorked])=1) Or (Weekday([DateWorked])=7),1,0))

and it worked fine.

Thanks so much NeoPa.

Jerry

[NeoPa]

So the solution that used In() didn't work. That's a shame. I felt sure it would.

Pleased it all works for you now anyway :)

[Jerry Maiapu]

So the solution that used In() didn't work. That's a shame. I felt sure it would.

Pleased it all works for you now anyway :)

Sorry NeoPa, I should have told you it worked but I am more comfortable with post# 8 option. But I think with In() is short and sweet so OK am convinced now that I'll use In(). Anyway, which one i tried at least I am happy that both could solve my problem. So ya thank you once again NeoPa you're of great help to me indeed.

Regards.

Jerry

[NeoPa]

No worries Jerry. I was only interested in the viability of it (whether it worked or not). What you choose to incorporate into your project is entirely up to you, and no business of mine.