Translating Excel IIF statement into Excel

I'm having some trouble turning a nested IF statement from Excel into an Access nested IIF query.
Here's the statement in Excel:
IF(ISERROR(VLOOKUP($I6,Criteria!Fact_States,1,FALSE)),"",(IF(M6>800,IF($E6<Criteria!$C$4,"FACT",(IF(M6>200,IF(M6<799,IF($E6<Criteria!$C$4,"FACT"),"")))),"")))

Criteria--Sheet
Fact_States ---Named range of a column of about 20 state codes.  My biggest problem is checking to see if column I6 is in Fact_States. I tried making a table of state codes with the IN statement but couldn't figure out a statement similar to vlookup.  I thought this would work:

FACT:IIF(State.Apps NOT IN States.tblFactStates, " ", (IIF(...

But the bigger problem is that I don't think I'm translating the if statement properly.  Any help would be GREATLY appreciated.
chrisamuelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bonjour-autCommented:
if you are talking about using this in an SQL statement, i recommend to use the switch fuction rather than nested Iif's.
it is faster and better to handle:

Switch(condtion1,code1,condtion2,code2,.......1=1,default code)

so it behaves like an Select Case in VBA, 1st condition met draws
if necessary, you can nest them also

Regards, Franz

0
chrisamuelAuthor Commented:
I'm trying to exactly recreate an excel spreadsheet with calculated columns using calculated expressions in a query, and would like to stick to nested ifs because at least I'm somewhat familiar with those.
0
chrisamuelAuthor Commented:
Ok, thx to zorvek, it's translated to this in Excel:

If Cell I6 is in the column Fact_States on sheet Criteria AND M6 > 800 AND ((Cell E6 < Cell C4 on sheet Criteria) OR (Cell M6 > 200 And Cell M6 < 400 And Cell E6 < Cell C4 on sheet Criteria)) then
   Value is "Fact"
Else
   Value is null
End

I don't want to do it in VBA because I'm trying to display it in datasheet view with calculated expressions in fields.  Can anybody please suggest a query statement that does this?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bonjour-autCommented:
OK

So try FACT: IIF(DLokkup("State","Apps","State='" & tblFactStates.States & "'"), " ", (IIF(...

or just use the correct naming:   tablename.fieldname in your sql


Regards, Franz
0
bonjour-autCommented:
did not see your last post when writing mine
slow reaction of the system actually

i am confused a little bit

do you want to code something in excel or are we talking about
a sql statement for a stored query in access ?

if it is access please state tablenames and fieldnames
- i am off untill tomorrow afternoon

regards, franz
0
chrisamuelAuthor Commented:
If Cell I6 is in the column Fact_States on sheet Criteria AND M6 > 800 AND ((Cell E6 < Cell C4 on sheet Criteria) OR (Cell M6 > 200 And Cell M6 < 400 And Cell E6 < Cell C4 on sheet Criteria)) then
   Value is "Fact"
Else
   Value is null
End

Query in Access---but I'm trying to recreate based on an IF statement in excel:

What's the best way to calculate:
If State.tbl1 is IN States.tblFactStates AND
PCode.tbl1 > 800 AND
CurrentAmt.tbl2 < MaxAmt.tbl2 OR
PCode.tbl1 > 200 AND PCode.tbl1 < 400 AND
PCode.tbl1 < MaxAmt.tbl2 THEN
"Fact"
ELSE
" "

0
harfangCommented:
I think I answered that in http:Q_21206603.html#12581296 ...
(just so that others reading this can rest... :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.