Count IIf - too complex to be evaluated

[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!

 

[Duane Hookom]

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

 

[Guest]

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!

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

--
Duane Hookom
MS Access MVP

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!