IIF statement two criteria

[Steven R via AccessMonster.com]

I have a column in a SELECT query that returns a value of 1 if the record is
under 5,000, zero if over 5,000

Cat0: IIf(([spenddataqrytbl].[POSub]<5000),1,0)

How would I add a similar column to do the same thing for cases of values
between 5,000-10,000 ?

 

[Tom Ellison]

Dear Steven:

Since your first "bracket" is < 5000, I'll assume your second bracket is >=
5000 and < 10000 (just to be somewhat consistent). So:

Cat1: IIf(spenddataqrytbl.POSub >= 5000 AND spenddataqeytbl.POSub < 1000, 1,
0)

Does this do it for you?

If you have a number of categories like this, it is good to build a table
of them and write a query that uses the table values to categorize. This
makes it easy to change categories, or add a whole new set of them, without
having to recode numerous queries. It's a lot easier to change data than to
reprogram. Most users could even handle that.

Tom Ellison

 

[Pat Hartman\(MVP\)]

Cat0: IIf([spenddataqrytbl].[POSub] < 5000,1,IIf([spenddataqrytbl].[POSub] <
10000, 2, 0))

IIf()'s contain three elements = condition/true path/false path. Either the
true path or the false path or both can contain another IIf() thereby
creating a nested If structure.