Count IIf - too complex to be evaluated

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, posted a couple of days ago "counts / sums in query / crosstab or
report" but didn't have much luck and have been spending my time googling for
solutions but still must be on the wrong track.

I have a report that is grouped on 4 levels (geographical breakdowns) and I
need to be able to count the details at each level (for inspections). I have
a count of the number of Inspections in the detail but cannot figure out how
to break it down further. For example, one column on the report is for the
Enf_Category (of the inspection) and it can have 5 possible answers. I need
to count the number of each Enf_Category at all 4 of my levels (District,
Subdistrict, Area, Field)

I've tried a number of things that didn't work at all but finally through
checking out a lot of sites I thought I had it:

=Count(IIf([Enforcement Category]="Minor",0))

in the footers of my report but I get the error message "the expression is
typed incorrectly or is too complex to be evaluated". I've also tried
creating additional columns in my query for expressions but get the same
error message.

I've read some bits about breaking down the query into smaller chunks and
doing subreports - I'm just not sure what to break out? The detail? I do
have a date parameter set on this as well (which I have to have) and I'm
wondering if that could be causing me problems?

Thanks for your help again!
 
Thanks Duane - I tried that too and it also didn't work - in fact I have torn
this apart every way from here to Sunday. Just now I realized the problem
and boy do I feel foolish! It turns out that in my query I had not included
the tables that have the look up data (such as Enf_Category) and since the
correct data kept showing up in my report I never thought about it. Redid
the query and report and now it's working great - hope someone else benefits
from my mistake!

Duane Hookom said:
Try:
=Sum(Abs([Enforcement Category]="Minor"))

--
Duane Hookom
MS Access MVP


carriey said:
Hello, posted a couple of days ago "counts / sums in query / crosstab or
report" but didn't have much luck and have been spending my time googling
for
solutions but still must be on the wrong track.

I have a report that is grouped on 4 levels (geographical breakdowns) and
I
need to be able to count the details at each level (for inspections). I
have
a count of the number of Inspections in the detail but cannot figure out
how
to break it down further. For example, one column on the report is for
the
Enf_Category (of the inspection) and it can have 5 possible answers. I
need
to count the number of each Enf_Category at all 4 of my levels (District,
Subdistrict, Area, Field)

I've tried a number of things that didn't work at all but finally through
checking out a lot of sites I thought I had it:

=Count(IIf([Enforcement Category]="Minor",0))

in the footers of my report but I get the error message "the expression is
typed incorrectly or is too complex to be evaluated". I've also tried
creating additional columns in my query for expressions but get the same
error message.

I've read some bits about breaking down the query into smaller chunks and
doing subreports - I'm just not sure what to break out? The detail? I do
have a date parameter set on this as well (which I have to have) and I'm
wondering if that could be causing me problems?

Thanks for your help again!
Click to expand...
Click to expand...
 
Back
Top