IIF statement for > and <

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

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))
 
Tina said:
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?
Click to expand...

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
 
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))
 
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..

Ofer said:
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))


Tina said:
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))
Click to expand...
Click to expand...
 
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))
Click to expand...

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.
 
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.
Click to expand...

This should do it:

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

Tom Lake
 
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, ?))

Tina said:
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..

Ofer said:
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))


Tina said:
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))
Click to expand...
Click to expand...
Click to expand...
 
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
 
Tina said:
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))
Click to expand...


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.
 
Back
Top