IIF statement for > and <

[Guest]

I'm trying to do a statement to group categories based on less/than values
(see below)

If I only do > and no <=, then all values will be "4". How can I write
this? Is it an Iif or ElseIf? Or am I way off?

Expr1: IIf(([CountOfIncident # ]>0 And [CountOfIncident # ]<=4),4,
elseIf([CountOfIncident # ]>4 And [CountOfIncident # ]<=7),3))

 

[Tom Lake]

I'm trying to do a statement to group categories based on less/than values
(see below)

If I only do > and no <=, then all values will be "4". How can I write
this? Is it an Iif or ElseIf? Or am I way off?

No elseif needed. Nest your IIf functions. Also, if CountOfIncident can
only be 0 - 7 you can eliminate the test for it being >4 in the second IIf.


Expr1: IIf(([CountOfIncident # ]>0 And [CountOfIncident # ]<=4),4,
IIf([CountOfIncident #]>4 And [CountOfIncident # ]<=7,3,"Not 1 - 7")

Tom Lake

 

[Guest]

It's iif
IIf(([CountOfIncident #]>0 And [CountOfIncident #]<=4),4,
IIf([CountOfIncident #]>4 And [CountOfIncident #]<=7),3))

But what if both condition are not relavent, then what should it be, I put 0
there.
IIf(([CountOfIncident #]>0 And [CountOfIncident #]<=4),4,
IIf([CountOfIncident #]>4 And [CountOfIncident #]<=7),3,0))

 

[Guest]

Thanks to both of you for responding.

When I try Ofer's suggestion, I get an error about the number of arguments..

When I try Tom's, I had an error about # of parenthesis or brackets..

It's iif
IIf(([CountOfIncident #]>0 And [CountOfIncident #]<=4),4,
IIf([CountOfIncident #]>4 And [CountOfIncident #]<=7),3))

But what if both condition are not relavent, then what should it be, I put 0
there.
IIf(([CountOfIncident #]>0 And [CountOfIncident #]<=4),4,
IIf([CountOfIncident #]>4 And [CountOfIncident #]<=7),3,0))

I'm trying to do a statement to group categories based on less/than values
(see below)

If I only do > and no <=, then all values will be "4". How can I write
this? Is it an Iif or ElseIf? Or am I way off?

Expr1: IIf(([CountOfIncident # ]>0 And [CountOfIncident # ]<=4),4,
elseIf([CountOfIncident # ]>4 And [CountOfIncident # ]<=7),3))

 

[fredg]

I'm trying to do a statement to group categories based on less/than values
(see below)

If I only do > and no <=, then all values will be "4". How can I write
this? Is it an Iif or ElseIf? Or am I way off?

Expr1: IIf(([CountOfIncident # ]>0 And [CountOfIncident # ]<=4),4,
elseIf([CountOfIncident # ]>4 And [CountOfIncident # ]<=7),3))

If the full range is only 0 to 7, then all you need is:

=IIf([CountOfIncident # ] > 0 And [CountOfIncident # ] <= 4, 4, 3)

If the full range is more than 0 to 7, and you are only interested
here in >0 and <= 7, then use (all on one line):

=IIf([CountOfIncident # ] > 0 And [CountOfIncident # ] <= 4, 4,
IIf([CountOfIncident # ] > 4 And [CountOfIncident # ] <= 7, 3), "")

No need for those extra sets of parenthesis.

 

[Tom Lake]

Thanks to both of you for responding.

When I try Ofer's suggestion, I get an error about the number of
arguments..

When I try Tom's, I had an error about # of parenthesis or brackets.

This should do it:

Expr1: IIf([CountOfIncident # ]>0 And [CountOfIncident # ]<=4,4,
IIf([CountOfIncident #]>4 And [CountOfIncident # ]<=7,3,"Not 1 - 7"))

Tom Lake

 

[Guest]

Tina,

I would suggest not using an immediate if (IIf) for this. It would be
easier to read if you use a regular If Then Else contstruct. I recommend
never nesting IIf's. They are hard to read. I only use an IIf when there
are only two choices. But, then everyone has their own style.

I would write it like this:
If CountOfIncident > 0 And CountOfIncident < = 4 Then
MyVar = 4
Else If [CountOfIncident #] > 4 And [CountOfIncident #] <= 7 Then
MyVar = 3
EndIf

If you insist on the IIf, this is the proper construct. The Argument not
optional problem is because there is not false return value in the second
IIf. (See the ? below) You must supply both a True and a False value for an
IIf.

IIf([CountOfIncident #] > 0 And [CountOfIndident #] < = 4,
4,IIf([CountOfIncident #] > 4 And [CountOfIndident #] < = 7, 3, ?))

Thanks to both of you for responding.

When I try Ofer's suggestion, I get an error about the number of arguments..

When I try Tom's, I had an error about # of parenthesis or brackets..

It's iif
IIf(([CountOfIncident #]>0 And [CountOfIncident #]<=4),4,
IIf([CountOfIncident #]>4 And [CountOfIncident #]<=7),3))

But what if both condition are not relavent, then what should it be, I put 0
there.
IIf(([CountOfIncident #]>0 And [CountOfIncident #]<=4),4,
IIf([CountOfIncident #]>4 And [CountOfIncident #]<=7),3,0))

I'm trying to do a statement to group categories based on less/than values
(see below)

If I only do > and no <=, then all values will be "4". How can I write
this? Is it an Iif or ElseIf? Or am I way off?

Expr1: IIf(([CountOfIncident # ]>0 And [CountOfIncident # ]<=4),4,
elseIf([CountOfIncident # ]>4 And [CountOfIncident # ]<=7),3))

 

[LGC]

If your input values are always in the range of 0 to 7 then the following
will give you what you want without the need of Iif:

Expr1: 4 + ([CountOfIncident # ] > 4)

-LGC

 

[Marshall Barton]

I'm trying to do a statement to group categories based on less/than values
(see below)

If I only do > and no <=, then all values will be "4". How can I write
this? Is it an Iif or ElseIf? Or am I way off?

Expr1: IIf(([CountOfIncident # ]>0 And [CountOfIncident # ]<=4),4,
elseIf([CountOfIncident # ]>4 And [CountOfIncident # ]<=7),3))

An Alternative to IIf in this kind of situation is the
Switch function:

Switch([CountOfIncident # ] Between 1 And 4, 4,
[CountOfIncident # ] Between 5 And 7, 3, True, ???)

In some situations, but not this one, the Partition function
can also be useful.

 

[Guest]

Clever, but a little obtuse.