UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Nested Iif - Help Please, Access 2016    
 
   
Brepea
post Jul 31 2020, 09:43 AM
Post#1



Posts: 624
Joined: 11-January 09
From: UK


Hi - can you help me with something i'm trying to work out:

Query Expression: Eligible (Yes or No is the option).

Actual Original Question is:
1) If [Div] is "USA", in "Bamland" and [Level]="A" set flag to "No", if [Div] is "Bamland" in "USA" and any other [Level] the set flag to "yes".
2) If [Div] is "USA", in "Mainland" and [Level]="A" set flag to "No", if [Div] is "Mainland" in "USA" and any other [Level] the set flag to "yes".
3) For all other [Div] set flag as "yes"
4) For all other [Div], if [ActiveO] >0 set flag as "yes" otherwise "no".

Expression is currently written as follows:
= IIf([Div]="USA",IIf([Country]="Bamland", IIf([Level]="A","no","yes"),IIf([Country]="Mainland",IIf([Level]="A","no","yes"),"yes")),IIf([ActiveO]>0,"yes","no"))

I would write this like this instead:

CODE
= iif([Div]="USA" AND [Country]="Bamland" OR [Country]="Mainland" AND [Level]="A","no",iif([ActiveO]>0,"yes","no"))


Don't the blue text pieces above close off the nested IIf too soon?

Which is the correct - or better?
This post has been edited by Brepea: Jul 31 2020, 10:32 AM
Go to the top of the page
 
theDBguy
post Jul 31 2020, 10:36 AM
Post#2


UA Moderator
Posts: 78,612
Joined: 19-June 07
From: SunnySandyEggo


Do they both work correctly?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Brepea
post Jul 31 2020, 10:53 AM
Post#3



Posts: 624
Joined: 11-January 09
From: UK


Not sure - i guess i always thought in an IIF statement - you can't close it off until last IIF - example:

1) iif([Sun]="red","y",iif([Sun]="yellow" and [sky]="blue","n",iif([Sun]="white" and [sky]="blue","y","n"))) (i always though you had to follow this method)
2) iif([Sun]="red",iif[Sun]="white",iif([sky]="blue","y","n"))),iif([Sun]="Yellow",iif([Sky]="blue","n","y")) - feels wrong - i mean doesn't the whole IIF statement end after ")))"

Can you have a full IIF statement nested in other IIF statements?: IIf(a=1,0,1),IIf(a=2,IIf(b-2,0,1)))? In this statement, I would have thought the IIF statement ended after the first ")" - and everything else in expression is ignored?
This post has been edited by Brepea: Jul 31 2020, 10:54 AM
Go to the top of the page
 
theDBguy
post Jul 31 2020, 11:22 AM
Post#4


UA Moderator
Posts: 78,612
Joined: 19-June 07
From: SunnySandyEggo


Hi. It all depends on what you want to happen. I think this is okay.

CODE
IIf(1=1,IIf(1=1,1,0),0)

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Brepea
post Jul 31 2020, 11:27 AM
Post#5



Posts: 624
Joined: 11-January 09
From: UK


Thanks - that helps - i didn't think you could have a "full IIF inside of a nested IIF" - your illustration is clear and therefore it seems you can!
Go to the top of the page
 
theDBguy
post Jul 31 2020, 11:47 AM
Post#6


UA Moderator
Posts: 78,612
Joined: 19-June 07
From: SunnySandyEggo


Of course. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th August 2020 - 05:04 AM