459,652 Members | 1,416 Online
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

 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  =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 =count([Overtime]=”AOT”) gives me the total count ignoring the condition Trying these Expand|Select|Wrap|Line Numbers =Count(where [Overtime]=”AOT”) Expand|Select|Wrap|Line Numbers  =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

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.

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.

10 Replies

 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

 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 =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

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

 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 =Count(IIf(Weekday([DateWorked])=1 Or 7,True,Null)) Expand|Select|Wrap|Line Numbers =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

 Expert Mod 15k+ P: 31,769 Try : Expand|Select|Wrap|Line Numbers =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 =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

 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

 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 =Sum(IIf((Weekday([DateWorked])=1) Or (Weekday([DateWorked])=7),1,0)) and it worked fine. Thanks so much NeoPa. Jerry Jul 6 '10 #8

 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

 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

 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