Count/Sum/Dcount from 2 criteria

[spartansFC]

Hi

I have a report based of a query. It's basically a summary report, so i only want to show totals etc.

I've managed to create totals based on one criteria

=Count(IIf([cApril2010]=Yes,0))

the above is placed in the header part of the report and works great.

but i'm stuck when i try to do a count or dcount based on 2 criteria, i have it sort of working using

=Abs(Sum([cSetting) is not null AND [cSept2009]=yes,0))

But the above lists everying (14 in total), i've tried changing the first part to [cSetting] ="" to only count when there is a value in that field as not all setting fields have a value. I was just going to do a subreport and add it to the main report but there must be a way of counting on 2 criteria.

i've attached a screenshot with dummy information on to show you what i mean. In the example, the report should only show that.

April 2009 = 0
Sept 2009 = 3
Jan 2010 = 3
April 2010 = 3
Sept 2010 = 0
Jan 2011 =0

Any ideas, thanks

Mikie

 

[dhookom]

If you want to count the number of records in a report's record source that match a specific condtion, the generic expression is:

=Sum(Abs( [Your Expression] ))

If you want to count the number where cSetting is not null and cSept2009 is true, I would try

=Sum(Abs([cSetting) is not null AND [cSept2009]=True))

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

Thanks for your reply which worked and it does give the result i'm asking the Sum statement to ask but as usual i'm not telling access what i really want.

the example i showed had 14 records in total, in Sept 2009 there are 7 "True's" which is what the report is showing.

But what i need it to say is, within those 14 records, if any of the cSettings have a setting, then look at Sept2009 and count if value is true.

so if i can figure out how to write that statement, the answer should be 3 "True's" in Sept 2009.

Mikie

 

[dhookom]

What type of field is cSetting? Do you think the field is truly null or maybe it has a value of 0 or a zero-length-string.

This expression might work better to count the records where cSept2009 is true and nothing seems to be stored in the cSettings field.

=Sum(Abs(Len(Trim([cSetting) & ""))= 0 AND [cSept2009]=True))

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

Thanks again for your reply, i used your

=Sum(Abs(Len(Trim([cSetting) & ""))= 0 AND [cSept2009]=True))

but tweaked it a bit. I don't think i gave you a good explanation. cSetting is a number field as it's a combo box which then looks at another table tblSetting which has SettingID, SettingName. So i put that into the query and changed the above code which is in the header of my report to:

=Sum(Abs(Len(Trim([SettingName) & ""))<> 0 AND [cSept2009]=True))

It worked straight away. Thanks again for your help. It's much appreciated.

Mikie