Busted IIf function


E

esn

I'm trying to summarize some vegetation data that involves putting the
trees into categories and classes based on their diameter and their
"status" (live versus dead). To get a count of the treees in each
category/class at each site in each year, I'm using a query that sums
an IIf statement that contains the criteria for each category. I'm
using this setup instead of simply counting the ID field so that the
query will return zeros for sites that don't have any trees in that
category (If I enter the criteria independently and then count the ID
field I'll get blanks where no records match the criteria). Here's an
example of the Sum(Iif(... function I'm trying to apply:

Sound Snags 24-36: Sum(IIf(60.96<[Trees]![DBH (cm)]<=91.44 And [Trees]!
[Status]="d" And [Trees]![Decay Class]<4,1,0))

For some reason the first of the three criteria is not being applied -
is my syntax screwed up? In the results I get a count of all the
trees with status=d and decay class<4, regardless of the value in the
"DBH (cm)" field.

Any ideas what I'm doing wrong?
 
Ad

Advertisements

K

KARL DEWEY

is my syntax screwed up?
It seems to be to me.

What is this suppose to be?
60.96 < [Trees]![DBH (cm)] <= 91.44
 
E

esn

I was going for numbers greater than 60.96 and less than or equal to
91.44. Turns out you can't write the expression that simply - I wrote
it as two separate statements with an 'and' and it works now.
 
Ad

Advertisements

M

Marshall Barton

esn said:
I'm trying to summarize some vegetation data that involves putting the
trees into categories and classes based on their diameter and their
"status" (live versus dead). To get a count of the treees in each
category/class at each site in each year, I'm using a query that sums
an IIf statement that contains the criteria for each category. I'm
using this setup instead of simply counting the ID field so that the
query will return zeros for sites that don't have any trees in that
category (If I enter the criteria independently and then count the ID
field I'll get blanks where no records match the criteria). Here's an
example of the Sum(Iif(... function I'm trying to apply:

Sound Snags 24-36: Sum(IIf(60.96<[Trees]![DBH (cm)]<=91.44 And [Trees]!
[Status]="d" And [Trees]![Decay Class]<4,1,0))

For some reason the first of the three criteria is not being applied -
is my syntax screwed up? In the results I get a count of all the
trees with status=d and decay class<4, regardless of the value in the
"DBH (cm)" field.


The syntax A<X<B doesn't mean what you think it does. Use
either:
Trees.[DBH (cm)] Between 60.97 And 91.44
or
60.96 < Trees![DBH (cm)] And Trees![DBH (cm)] <= 91.44
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top