asked on

Nested If statement in Access Query

Can someone please combine these 4 IF statement that i use currently in Access 2003 query window to one SINGLE Iff statement please.

IIf([16Feb_Jetbase_2_XCOLL]!EBK_MATURITY<#16/02/2009# And [16Feb_Jetbase_2_XCOLL]![Last Date Stored]<#16/02/2009#,1,0)

IIf([16Feb_Jetbase_2_XCOLL]!EBK_MATURITY<#16/02/2009# And [16Feb_Jetbase_2_XCOLL]!RISKCL="161A",1,0)

IIf([16Feb_Jetbase_2_XCOLL]!EBK_MATURITY<#16/02/2009# And [16Feb_Jetbase_2_XCOLL]!EBK_TV Between -1 And 1,1,0)

IIf([16Feb_Jetbase_2_XCOLL]!EBK_MATURITY<#16/02/2009# And [16Feb_Jetbase_2_XCOLL]![MARKETER (DEAL_TYPE_VALUE)]<>"AMORT_ABX" And "AMORT_CDS" And "CANCELLABLE" And "DEFAULT_SWP" And "EARLY_TERM_BERMUDA" And "EUROPEAN" And "FEE_ONLY" And "IR_SWAP" And "TR_SWP",1,0)

Regards
Siva

peter57r

Only a masochist would agree to do what are asking (:-) and such facts are not part of the user profile.

Scott McDaniel (EE MVE )

Well we're not really here to do your work for you, and it's very difficult for us to test without your specific database structure.

It's not too difficult to nest IIF statements if you take your time and do one at a time:

IIF(SomeValue=1, "B", IIF(SomeValue=2, "A", "C")

Just work through them in this fashion, checking after EACH new IIF statement is added to make sure your syntax is correct.

ASKER CERTIFIED SOLUTION

fabriciofonseca

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

siva_iaf

ASKER

HI fabriciofonseca:

Apologies...Your statement is absolutely Correct and it is working...but I had given the points and closed it b4 ur answer..dont know how to get that back and allocate to you..is it possible ?

Regards

Scott McDaniel (EE MVE )

You can use the Request Attention link to ask the Mods to reopen your question ...

Jeffrey Coachman

siva_iaf,

FWIW,

When you have Nested IIF functions this long, and complex, you really should perhaps look into creating a VBA Function.

1. I will probably execute faster.
2. It will be much easier to read and understand. ;-O
3. I can be commented.
4. It will be "portable", meaning you could easilly use it in another databse or different section of your application.
5. It will be a lot easier to add AND and OR logic.
...ect

;-)

Jeff

siva_iaf

ASKER

Thanks Jeff
I am Ok only with Acces Query Window as I dont know ABC's of VBA.
Reg

Jeffrey Coachman