?
Solved

Translating Excel IIF statement into Excel

Posted on 2004-11-14
7
Medium Priority
?
1,852 Views
Last Modified: 2010-05-18
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.
0
Comment
Question by:chrisamuel
  • 3
  • 3
7 Comments
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 12580410
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
 

Author Comment

by:chrisamuel
ID: 12580560
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
 

Author Comment

by:chrisamuel
ID: 12580606
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 18

Expert Comment

by:bonjour-aut
ID: 12580615
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
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 12580650
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
 

Author Comment

by:chrisamuel
ID: 12580678
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
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 12583202
I think I answered that in http:Q_21206603.html#12581296 ...
(just so that others reading this can rest... :)
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question