Update Query. How Many Iif Statements Are Allowed?
Jan 30, 2008
Hi,
I am going to be creating a iif statement update qery, to update a column on dates, E.g. would be....
IIf([FinMonth] between 01/01/2008 and 31/01/2008,1)iif([FinMonth] between 01/02/2008 and 30/02/2008,2)iif(etc etc
2 questions??
1) Is 12 nested iif's statements are allowed within one IIF statement?
2) iS THERE AN EASIER WAY TO DO THIS??
Hope this explains enough.
Thanks
View Replies
Sep 7, 2005
:confused:
Hi I need help inserting IIF statements as criterias in my query.
Here's the query I have created from a table called XML_EXPORT which consists of a list of ORGANIZATIONS with various fields. What I'm trying to do is display:
-the name of the EXEC_NAME_1 (primary executive's name)
-the ADDRESS
-the CITY
-the POSTAL_CODE
-the PROVINCE
-the PUBLICATION
-the ORG_LEVEL_1 (the organization's main name)
-the ORG_LEVEL_2 (the organization's departments 2 to 5)
-the ORG_LEVEL_3
-the ORG_LEVEL_4
-the ORG_LEVEL_5
Now not every record contains data in ORG_LEVEL_2 to ORG_LEVEL_5 so we want to display only ORG_LEVEL_1 and the last ORG_LEVEL that is not blank.
i.e. If a record has ORG_LEVEL_4 and ORG_LEVEL_5 blank then we want to only display: ORG_LEVEL_1 and ORG_LEVEL_3 (even though there is something in ORG_LEVEL_2)
Here's my query:
------------------------------------------
SELECT XML_EXPORT.EXEC_NAME_1, XML_EXPORT.ADDRESS, XML_EXPORT.CITY, XML_EXPORT.POSTAL_CODE, XML_EXPORT.PROVINCE, XML_EXPORT.PUBLICATION, XML_EXPORT.ORG_LEVEL_1, XML_EXPORT.ORG_LEVEL_2, XML_EXPORT.ORG_LEVEL_3, XML_EXPORT.ORG_LEVEL_4, XML_EXPORT.ORG_LEVEL_5
FROM XML_EXPORT
WHERE (((XML_EXPORT.ADDRESS) Is Not Null));
------------------------------------------
Can anyone show me how the Iif statement syntax would be ?
Allan
View 2 Replies
View Related
Mar 16, 2007
I had been using some SQL in Access with many IIf statements. I understand the equivalent in SQL is CASE, WHEN, END.
However, I'm really stuck with the following:
oh..ISNULL used to be Nz as well
IIf(ISNULL(ZI.InvoicesRaised,0)=0,0,IIf(IIf(ISNULL (ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)>0,30,(ISNULL(ZA.TotalDebt,0)*30)/ISNULL(ZI.InvoicesRaised,0)),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)>0,30,(ISNULL(ZA.TotalDebt,0)*30)/ISNULL(ZI.InvoicesRaised,0)),0),0))
+IIf(ISNULL(ZIMinus1.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0))*30)/ISNULL(ZIMinus1.InvoicesRaised,0))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0))*30)/ISNULL(ZIMinus1.InvoicesRaised,0))),0),0))
+IIf(ISNULL(ZIMinus2.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0))*30)/(ISNULL(ZIMinus2.InvoicesRaised,0)))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0))*30)/(ISNULL(ZIMinus2.InvoicesRaised,0)))),0),0))
+IIf(ISNULL(ZIMinus3.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0))*30)/(ISNULL(ZIMinus3.InvoicesRaised,0)))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0))*30)/(ISNULL(ZIMinus3.InvoicesRaised,0)))),0),0))
+IIf(ISNULL(ZIMinus4.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)-ISNULL(ZIMinus4.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0))*30)/(ISNULL(ZIMinus4.InvoicesRaised,0)))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)-ISNULL(ZIMinus4.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0))*30)/(ISNULL(ZIMinus4.InvoicesRaised,0)))),0),0)) AS DD
Do I have to put in some seriously heavyily nested CASE statements or are there some other ways round this?
View 8 Replies
View Related