Multiple IIF - Help me

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

Guest

=IIf([approved_amt] Between 5000 And 10001,100,IIf([approved_amt] Between
11000 And 20001,200),IIf([approved_amt] Between 21000 And 30001,300))

Can someone help me with this statement..

I tried to get this result.

Range Amt
5000 - 10000 100
11000 - 20000 200
21000 - 30000 300
 
Try this

=IIf([approved_amt] >= 5000 And [approved_amt]
<=10001,100,IIf([approved_amt] >= 11000 And [approved_amt] < =
20001,200,IIf([approved_amt] >= 21000 And [approved_amt] <= 30001,300)))
 
I tried but still invalid syntax.

Appreciate your response

Ofer said:
Try this

=IIf([approved_amt] >= 5000 And [approved_amt]
<=10001,100,IIf([approved_amt] >= 11000 And [approved_amt] < =
20001,200,IIf([approved_amt] >= 21000 And [approved_amt] <= 30001,300)))

--
In God We Trust - Everything Else We Test


zyus said:
=IIf([approved_amt] Between 5000 And 10001,100,IIf([approved_amt] Between
11000 And 20001,200),IIf([approved_amt] Between 21000 And 30001,300))

Can someone help me with this statement..

I tried to get this result.

Range Amt
5000 - 10000 100
11000 - 20000 200
21000 - 30000 300
Click to expand...
Click to expand...
 
You right, I forgot in the last iif statement to give a value if none of the
conditions worked.
I used 0, you need to put the value you want to be displayed if none of the
conditions worked

=IIf([approved_amt] >= 5000 And [approved_amt]
<=10001,100,IIf([approved_amt] >= 11000 And [approved_amt] < =
20001,200,IIf([approved_amt] >= 21000 And [approved_amt] <= 30001,300,0)))


--
In God We Trust - Everything Else We Test


zyus said:
I tried but still invalid syntax.

Appreciate your response

Ofer said:
Try this

=IIf([approved_amt] >= 5000 And [approved_amt]
<=10001,100,IIf([approved_amt] >= 11000 And [approved_amt] < =
20001,200,IIf([approved_amt] >= 21000 And [approved_amt] <= 30001,300)))

--
In God We Trust - Everything Else We Test


zyus said:
=IIf([approved_amt] Between 5000 And 10001,100,IIf([approved_amt] Between
11000 And 20001,200),IIf([approved_amt] Between 21000 And 30001,300))

Can someone help me with this statement..

I tried to get this result.

Range Amt
5000 - 10000 100
11000 - 20000 200
21000 - 30000 300
Click to expand...
Click to expand...
Click to expand...
 
Still cant get thru...

Ofer said:
You right, I forgot in the last iif statement to give a value if none of the
conditions worked.
I used 0, you need to put the value you want to be displayed if none of the
conditions worked

=IIf([approved_amt] >= 5000 And [approved_amt]
<=10001,100,IIf([approved_amt] >= 11000 And [approved_amt] < =
20001,200,IIf([approved_amt] >= 21000 And [approved_amt] <= 30001,300,0)))


--
In God We Trust - Everything Else We Test


zyus said:
I tried but still invalid syntax.

Appreciate your response

Ofer said:
Try this

=IIf([approved_amt] >= 5000 And [approved_amt]
<=10001,100,IIf([approved_amt] >= 11000 And [approved_amt] < =
20001,200,IIf([approved_amt] >= 21000 And [approved_amt] <= 30001,300)))

--
In God We Trust - Everything Else We Test


:

=IIf([approved_amt] Between 5000 And 10001,100,IIf([approved_amt] Between
11000 And 20001,200),IIf([approved_amt] Between 21000 And 30001,300))

Can someone help me with this statement..

I tried to get this result.

Range Amt
5000 - 10000 100
11000 - 20000 200
21000 - 30000 300
Click to expand...
Click to expand...
Click to expand...
 
What do you want if it's less that 5000, or greater than 30000? I'll assume
0 in the answer below:

=IIf([approved_amt] Between 5000 And 10001,100,IIf([approved_amt] Between
11000 And 20001,200,IIf([approved_amt] Between 21000 And 30001,300,0)))
 
Try this. It's a bit simpler.
IIf([approved_amt] <= 10000, 100, IIf([approved_amt] <= 20000, 200,
IIf([approved_amt] <= 30000, 300, 0)))

The above code will return 0 if [approved_amt] > 30000, which is something
you haven't considered. Having said that, my code does not consider
situations where [approved_amt] < 0.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Do you get an error message, or you just don't get the resault you expected
to get?
Where do you use this IIF statement, Form, Report, Query?
Does [approved_amt] always contain a value, or it's sometimed NULL?
(Null value can return an error)
Where do you get the [approved_amt] value from?

--
In God We Trust - Everything Else We Test


zyus said:
Still cant get thru...

Ofer said:
You right, I forgot in the last iif statement to give a value if none of the
conditions worked.
I used 0, you need to put the value you want to be displayed if none of the
conditions worked

=IIf([approved_amt] >= 5000 And [approved_amt]
<=10001,100,IIf([approved_amt] >= 11000 And [approved_amt] < =
20001,200,IIf([approved_amt] >= 21000 And [approved_amt] <= 30001,300,0)))


--
In God We Trust - Everything Else We Test


zyus said:
I tried but still invalid syntax.

Appreciate your response

:

Try this

=IIf([approved_amt] >= 5000 And [approved_amt]
<=10001,100,IIf([approved_amt] >= 11000 And [approved_amt] < =
20001,200,IIf([approved_amt] >= 21000 And [approved_amt] <= 30001,300)))

--
In God We Trust - Everything Else We Test


:

=IIf([approved_amt] Between 5000 And 10001,100,IIf([approved_amt] Between
11000 And 20001,200),IIf([approved_amt] Between 21000 And 30001,300))

Can someone help me with this statement..

I tried to get this result.

Range Amt
5000 - 10000 100
11000 - 20000 200
21000 - 30000 300
Click to expand...
Click to expand...
Click to expand...
 
Sorry, my brain only saw the upper thresholds of 10000, 20000 and 30000, not
the lower thresholds. That being the case, Doug's code will do it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham R Seach said:
Try this. It's a bit simpler.
IIf([approved_amt] <= 10000, 100, IIf([approved_amt] <= 20000, 200,
IIf([approved_amt] <= 30000, 300, 0)))

The above code will return 0 if [approved_amt] > 30000, which is something
you haven't considered. Having said that, my code does not consider
situations where [approved_amt] < 0.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

zyus said:
=IIf([approved_amt] Between 5000 And 10001,100,IIf([approved_amt] Between
11000 And 20001,200),IIf([approved_amt] Between 21000 And 30001,300))

Can someone help me with this statement..

I tried to get this result.

Range Amt
5000 - 10000 100
11000 - 20000 200
21000 - 30000 300
Click to expand...
Click to expand...
 
Its an error message. The IIF is ini report and approved amt always contain
value.

But now its fix with Doug's statement. It's due to parentheses error.

Thanks

Ofer said:
Do you get an error message, or you just don't get the resault you expected
to get?
Where do you use this IIF statement, Form, Report, Query?
Does [approved_amt] always contain a value, or it's sometimed NULL?
(Null value can return an error)
Where do you get the [approved_amt] value from?

--
In God We Trust - Everything Else We Test


zyus said:
Still cant get thru...

Ofer said:
You right, I forgot in the last iif statement to give a value if none of the
conditions worked.
I used 0, you need to put the value you want to be displayed if none of the
conditions worked

=IIf([approved_amt] >= 5000 And [approved_amt]
<=10001,100,IIf([approved_amt] >= 11000 And [approved_amt] < =
20001,200,IIf([approved_amt] >= 21000 And [approved_amt] <= 30001,300,0)))


--
In God We Trust - Everything Else We Test


:

I tried but still invalid syntax.

Appreciate your response

:

Try this

=IIf([approved_amt] >= 5000 And [approved_amt]
<=10001,100,IIf([approved_amt] >= 11000 And [approved_amt] < =
20001,200,IIf([approved_amt] >= 21000 And [approved_amt] <= 30001,300)))

--
In God We Trust - Everything Else We Test


:

=IIf([approved_amt] Between 5000 And 10001,100,IIf([approved_amt] Between
11000 And 20001,200),IIf([approved_amt] Between 21000 And 30001,300))

Can someone help me with this statement..

I tried to get this result.

Range Amt
5000 - 10000 100
11000 - 20000 200
21000 - 30000 300
Click to expand...
Click to expand...
Click to expand...
 
It works.

Thanks a lot.

zyus

Douglas J Steele said:
What do you want if it's less that 5000, or greater than 30000? I'll assume
0 in the answer below:

=IIf([approved_amt] Between 5000 And 10001,100,IIf([approved_amt] Between
11000 And 20001,200,IIf([approved_amt] Between 21000 And 30001,300,0)))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


zyus said:
=IIf([approved_amt] Between 5000 And 10001,100,IIf([approved_amt] Between
11000 And 20001,200),IIf([approved_amt] Between 21000 And 30001,300))

Can someone help me with this statement..

I tried to get this result.

Range Amt
5000 - 10000 100
11000 - 20000 200
21000 - 30000 300
Click to expand...
Click to expand...
 
Thanks for your respond

Graham R Seach said:
Sorry, my brain only saw the upper thresholds of 10000, 20000 and 30000, not
the lower thresholds. That being the case, Doug's code will do it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham R Seach said:
Try this. It's a bit simpler.
IIf([approved_amt] <= 10000, 100, IIf([approved_amt] <= 20000, 200,
IIf([approved_amt] <= 30000, 300, 0)))

The above code will return 0 if [approved_amt] > 30000, which is something
you haven't considered. Having said that, my code does not consider
situations where [approved_amt] < 0.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

zyus said:
=IIf([approved_amt] Between 5000 And 10001,100,IIf([approved_amt] Between
11000 And 20001,200),IIf([approved_amt] Between 21000 And 30001,300))

Can someone help me with this statement..

I tried to get this result.

Range Amt
5000 - 10000 100
11000 - 20000 200
21000 - 30000 300
Click to expand...
Click to expand...
Click to expand...
 
Back
Top