[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5402
  • Last Modified:

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
0
siva_iaf
Asked:
siva_iaf
  • 2
  • 2
  • 2
  • +2
1 Solution
 
peter57rCommented:
Only a masochist would agree to do what are asking (:-)  and such facts are not part of the user profile.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
fabriciofonsecaCommented:
IIf(([16Feb_Jetbase_2_XCOLL]!EBK_MATURITY<#16/02/2009# And [16Feb_Jetbase_2_XCOLL]![Last Date Stored]<#16/02/2009#) or ([16Feb_Jetbase_2_XCOLL]!EBK_MATURITY<#16/02/2009# And [16Feb_Jetbase_2_XCOLL]!RISKCL="161A") or ([16Feb_Jetbase_2_XCOLL]!EBK_MATURITY<#16/02/2009# And [16Feb_Jetbase_2_XCOLL]!EBK_TV Between -1 And 1) or ([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)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
siva_iafAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use the Request Attention link to ask the Mods to reopen your question ...
0
 
Jeffrey CoachmanCommented:
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
0
 
siva_iafAuthor Commented:
Thanks Jeff
I am Ok only with Acces Query Window as I dont know ABC's of VBA.
Reg
0
 
Jeffrey CoachmanCommented:
ok
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now