Help with Mutiple Iif statements

[whuegel]

Hi I'm trying to build a query field that returns a label based on total sales of an account. When I try to embed multiple Iif's into a single statement I am recieving an error. Can you help me?

SALES TIER: IIf([SALES]>750000,"Tier 4 - >$750,000",IIf([SALES]<250000,"Tier 1 - <$250,000",IIf([SALES]>250000 And [SALES]<500000,"Tier 2 - $250,000 - $500,000",IIf([SALES]>500000 And [SALES]<750000,"Tier 3 - $500,000 - $750,000","ERROR"))))

In English:

If Sales > $750,000 return "Tier 4 - >$750,000"
If Sales < $250,000 return "Tier 1 - <$250,000"
If Sales are between $250,000 and $500,000 return "Tier 2 - $250,000 - $500,000"
If Sales are between $500,000 and $750,000" return "Tier 3 - $500,000 - $750,000"

 

[craig.penny]

Hi whuegel and welcome to the forum

Would something like this work for you:

=IF(A1<250000,"Tier 1",IF(A1<500000,"Tier 2",IF(A1<750000,"Tier 3",IF(A1>=750000,"Tier 4"))))

?

I've used worksheet range A1 in place of your [SALES] so I could test it out.

 

[whuegel]

Hi whuegel and welcome to the forum

Would something like this work for you:

=IF(A1<250000,"Tier 1",IF(A1<500000,"Tier 2",IF(A1<750000,"Tier 3",IF(A1>=750000,"Tier 4"))))

?

I've used worksheet range A1 in place of your [SALES] so I could test it out.

Thanks!

 

[craig.penny]

Is it doing what you need?