By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,483 Members | 1,039 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,483 IT Pros & Developers. It's quick & easy.

Multiple Criteria for an IIF Statement in Access

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

Ultimately I want it to do the following:

Expand|Select|Wrap|Line Numbers
  1. IIF amount<0, 1
  2. IIF amount>=0 and amount <100, 2
  3. IIF amount >=100 and amount <1000, 3
and so on...

Can anyone help?
Oct 19 '11 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 10K+
P: 12,365
You can nest them together
Expand|Select|Wrap|Line Numbers
  1. iif(expression1, 1, iif(expression2, 2, ... and so on))
Oct 19 '11 #2

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

Expand|Select|Wrap|Line Numbers
  1. Update EE_Detail_Tab_1
  2. set STRAT_FIELD=
  3. IIF([VARIN REPCUR]<0), 1,
  4. IIF (([VARIN REPCUR]>=0 and [VARIN REPCUR] <100), 2,
  5. IIF (([VARIN REPCUR] >=100 and [VARIN REPCUR] <1000), 3))
The error I received was "Syntax error in Update Statement"
Oct 19 '11 #3

Expert Mod 10K+
P: 12,365
Your parentheses are out of place and you have spaces between the function name and the opening parentheses.
Oct 19 '11 #4

Expert Mod 15k+
P: 31,476
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.
Oct 19 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.