By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,652 Members | 1,416 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,652 IT Pros & Developers. It's quick & easy.

How to Count field values with criteria in Report

Jerry Maiapu
100+
P: 259
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.
Expand|Select|Wrap|Line Numbers
  1.  =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
Expand|Select|Wrap|Line Numbers
  1. =count([Overtime]=”AOT”)
gives me the total count ignoring the condition
Trying these
Expand|Select|Wrap|Line Numbers
  1. =Count(where [Overtime]=”AOT”)
Expand|Select|Wrap|Line Numbers
  1.  =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
Jul 5 '10 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. =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.

Share this Question
Share on Google+
10 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Jul 5 '10 #2

NeoPa
Expert Mod 15k+
P: 31,769
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 :
Expand|Select|Wrap|Line Numbers
  1. =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.
Jul 5 '10 #3

Jerry Maiapu
100+
P: 259
Thanks Nico and NeoPa. NeoPa's solution worked perfectly.
Jul 6 '10 #4

Jerry Maiapu
100+
P: 259
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:
Expand|Select|Wrap|Line Numbers
  1. =Count(IIf(Weekday([DateWorked])=1 Or 7,True,Null))
Expand|Select|Wrap|Line Numbers
  1. =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
Jul 6 '10 #5

NeoPa
Expert Mod 15k+
P: 31,769
Try :
Expand|Select|Wrap|Line Numbers
  1. =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.
Expand|Select|Wrap|Line Numbers
  1. =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.
Jul 6 '10 #6

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 6 '10 #7

Jerry Maiapu
100+
P: 259
Thanks for the solution:
Combining your suggestions of Sum()Post#7 and solution Post#6 I did this
Expand|Select|Wrap|Line Numbers
  1. =Sum(IIf((Weekday([DateWorked])=1) Or (Weekday([DateWorked])=7),1,0))
and it worked fine.

Thanks so much NeoPa.

Jerry
Jul 6 '10 #8

NeoPa
Expert Mod 15k+
P: 31,769
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 :)
Jul 7 '10 #9

Jerry Maiapu
100+
P: 259
@NeoPa
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
Jul 7 '10 #10

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 7 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.