Multiple Criteria for an IIF Statement in Access

[W Sauce]

I am having trouble with creating an IIF Statement with multiple criteria.

Ultimately I want it to do the following:

IIF amount<0, 1
IIF amount>=0 and amount <100, 2
IIF amount >=100 and amount <1000, 3

and so on...

Can anyone help?

[Rabbit]

You can nest them together

iif(expression1, 1, iif(expression2, 2, ... and so on))

[W Sauce]

Thanks. I tried it, but still got an error. This is what I wrote:

Update EE_Detail_Tab_1
set STRAT_FIELD=
IIF([VARIN REPCUR]<0), 1,
IIF (([VARIN REPCUR]>=0 and [VARIN REPCUR] <100), 2,
IIF (([VARIN REPCUR] >=100 and [VARIN REPCUR] <1000), 3))

The error I received was "Syntax error in Update Statement"

[Rabbit]

Your parentheses are out of place and you have spaces between the function name and the opening parentheses.

[NeoPa]

That's because you haven't nested them properly. Check out your parentheses.

PS. I presume this is SQL code and not VBA. You didn't mention in your question.