SQL Query Problem!!!

[DaniBoy]

Hello, I created aql query on the design view and then switch it to sql view, I studied the code and figure out what was going on. This is what I had:

SELECT WorkOrders.WorkorderID, Addresses.[Parcel#], WorkOrders.NewCheckBox, WorkOrders.DateDelivered, WorkOrders.FiscalYear, Trim([XST_FNAM1]) & " " & Trim([XST_FNAM2]) & " " & Trim([XST_LNAME]) AS Expr1, APLUSDATA_XSTATEPF.XST_FNAM1, APLUSDATA_XSTATEPF.XST_FNAM2, APLUSDATA_XSTATEPF.XST_LNAME, WorkOrders.NewCartNum, WorkOrders.NewCartSize, IIf([AP_DATA1_SPCD1]>[newcartsizecode] And DatePart("q",[datedelivered])=1"1.00",
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=2"1.05",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=2"1.14",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=2"1.07",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=2"1.23",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=2"1.14",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0164" And DatePart("q",[datedelivered])=2"1.06",Null))))))))))))) AS Adjustment, IIf([newcartsize]="20Gal","0120",IIf([newcartsize]="35Gal","0135",IIf([newcartsize]="64Gal","0164",IIf([newcartsize]="96Gal","0196",Null)))) AS NewCartSizeCode, APLUSDATA_DATA1LF01.AP_DATA1_SPCD1, APLUSDATA_DATA1LF01.AP_DATA1_SPAMT1, APLUSDATA_DATA1LF01.AP_DATA1_SPCD2, APLUSDATA_DATA1LF01.AP_DATA1_SPAMT2, APLUSDATA_DATA1LF01.AP_DATA1_SPAMT3, APLUSDATA_XSTATEPF.XST_ADD1, APLUSDATA_XSTATEPF.XST_ADD2, APLUSDATA_XSTATEPF.XST_ADD3, APLUSDATA_XSTATEPF.XST_CITY, APLUSDATA_XSTATEPF.XST_STATE, APLUSDATA_XSTATEPF.XST_ZIP
FROM ((Addresses INNER JOIN WorkOrders ON Addresses.AddressID = WorkOrders.AddressID) INNER JOIN APLUSDATA_DATA1LF01 ON Addresses.[Parcel#] = APLUSDATA_DATA1LF01.DATA1_PROP) INNER JOIN APLUSDATA_XSTATEPF ON APLUSDATA_DATA1LF01.DATA1_PROP = APLUSDATA_XSTATEPF.XST_PROP
WHERE (((WorkOrders.NewCheckBox)=False) AND ((WorkOrders.DateDelivered) Between [Beginning Date] And [Ending Date]) AND ((WorkOrders.NewCartSize) Is Not Null) AND ((APLUSDATA_DATA1LF01.AP_DATA1_SPAMT1) Like 1) AND ((APLUSDATA_DATA1LF01.AP_DATA1_SPAMT3) Like 0))
ORDER BY WorkOrders.WorkorderID;

That basicly gives me a result of the workorders that had decrease on garbage cart size during the 1st and 2nd quater of the year.

Then I copy and pasted he following to make the 3rd Quater appear:

IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=3,"1.05",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=3,"1.14",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=3,"1.07",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=3,"1.23",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=3,"1.14",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0164" And DatePart("q",[datedelivered])=3,"1.06",

All was working good, I then tried to do a copy and paste for the 4th Quater but I get an error. I checked the code and everything seems to be just like the others. This is the whole sql code.

SELECT WorkOrders.WorkorderID, Addresses.[Parcel#], WorkOrders.NewCheckBox, WorkOrders.DateDelivered, WorkOrders.FiscalYear, Trim([XST_FNAM1]) & " " & Trim([XST_FNAM2]) & " " & Trim([XST_LNAME]) AS Expr1, APLUSDATA_XSTATEPF.XST_FNAM1, APLUSDATA_XSTATEPF.XST_FNAM2, APLUSDATA_XSTATEPF.XST_LNAME, WorkOrders.NewCartNum, WorkOrders.NewCartSize, IIf([AP_DATA1_SPCD1]>[newcartsizecode] And DatePart("q",[datedelivered])=4,"1.00",
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=1,"1.05",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=1,"1.14",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=1,"1.07",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=1,"1.23",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=1,"1.14",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0164" And DatePart("q",[datedelivered])=1,"1.06",
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=2,"1.05",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=2,"1.14",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=2,"1.07",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=2,"1.23",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=2,"1.14",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0164" And DatePart("q",[datedelivered])=2,"1.06",
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=3,"1.05",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=3,"1.14",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=3,"1.07",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=3,"1.23",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=3,"1.14",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0164" And DatePart("q",[datedelivered])=3,"1.06",Null))))))))))))) AS Adjustment, IIf([newcartsize]="20Gal","0120",IIf([newcartsize]="35Gal","0135",IIf([newcartsize]="64Gal","0164",IIf([newcartsize]="96Gal","0196",Null)))) AS NewCartSizeCode, APLUSDATA_DATA1LF01.AP_DATA1_SPCD1, APLUSDATA_DATA1LF01.AP_DATA1_SPAMT1, APLUSDATA_DATA1LF01.AP_DATA1_SPCD2, APLUSDATA_DATA1LF01.AP_DATA1_SPAMT2, APLUSDATA_DATA1LF01.AP_DATA1_SPAMT3, APLUSDATA_XSTATEPF.XST_ADD1, APLUSDATA_XSTATEPF.XST_ADD2, APLUSDATA_XSTATEPF.XST_ADD3, APLUSDATA_XSTATEPF.XST_CITY, APLUSDATA_XSTATEPF.XST_STATE, APLUSDATA_XSTATEPF.XST_ZIP
FROM ((Addresses INNER JOIN WorkOrders ON Addresses.AddressID = WorkOrders.AddressID) INNER JOIN APLUSDATA_DATA1LF01 ON Addresses.[Parcel#] = APLUSDATA_DATA1LF01.DATA1_PROP) INNER JOIN APLUSDATA_XSTATEPF ON APLUSDATA_DATA1LF01.DATA1_PROP = APLUSDATA_XSTATEPF.XST_PROP
WHERE (((WorkOrders.NewCheckBox)=False) AND ((WorkOrders.DateDelivered) Between [Beginning Date] And [Ending Date]) AND ((WorkOrders.NewCartSize) Is Not Null) AND ((APLUSDATA_DATA1LF01.AP_DATA1_SPAMT1) Like 1) AND ((APLUSDATA_DATA1LF01.AP_DATA1_SPAMT3) Like 0))
ORDER BY WorkOrders.WorkorderID;

The error that I get is as follow:

Expression to Complex in query expression "IIf([AP_DATA1_SPCD1]>[newcartsizecode] And DatePart("q",[datedelivered])=4,"1.00",
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0120" And DatePart,

I clicked on help and it says that :
The expression you typed is not valid for the reason indicated in the message. Make sure you have typed field names and punctuation correctly, and then try the operation again.


Can someone help me!!! this is realy holding me up!!!! Please try the code.

Is there a limit on how many characters the sql query can hold? Is that my problem!!?

DaniBoy!!!!

 

[Jon K]

In the query for the 4th Quarter, you have used 19 "IIf(" for "AS Adjustment", so there should also be 19 close brackets. But there are only 13 ")" there.

Hope it helps.

 

[DaniBoy]

Tried that and still Bad!!

Thanks
I did see that and added the rest of the brackets, I still get the same error, one thing, after I click on the OK button on the error msg, I get a blocked flashing curser on an equal "=" sing on the first if statement of the 4th quarter code. I put "This one" in front of and after the equal sing "=" on the code for you to know what I am talking about.

IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]This one = This one"0120" And DatePart("q",[datedelivered])=4,"1.14",


I can send the query if yopu would like me too.

DaniBoy

 

[Jon K]

Please attach your database here with the 4th quarter query and the necessary tables (with some test data). Compact the database before zipping it as there is a file size limit here.

Maybe someone else can spot something even if I can't.

I have both Access 97 and Access 2000.

 

[DaniBoy]

Thanks

Ok, I will upload the DB, I will have to get the data size small for zipping it.

Thanks
DaniBoy

 

[DaniBoy]

Hello Agian

Well here is the DB!! Finaly!!! The AS400 where I get the tables was down!! Then I made it small , it was 73 megas I made it 760 kb.

I hope you see what i mean by the error.

Goto queries and look at the Correction Forms 1&2&3 Decrease or Increase in desing view, try to add this to it:

IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=3,"1.05",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=3,"1.14",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=3,"1.07",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=3,"1.23",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=3,"1.14",
IIf([AP_DATA1_SPCD1]="0196" And [newcartsizecode]="0164" And DatePart("q",[datedelivered])=3,"1.06"

before the "Null)))))))))))) AS Adjustment

then try to save it.

Thanks !!
DaniBoy

Well I cant upload, it says is to big!!!

 

[Jon K]

I was able to run the following query nested with 13 iif():-

SELECT iif(day(Date())=1,1, iif(day(Date())=2,2, iif(day(Date())=3,3,
iif(day(Date())=4,4, iif(day(Date())=5,5, iif(day(Date())=6,6,
iif(day(Date())=7,7, iif(day(Date())=8,8, iif(day(Date())=9,9,
iif(day(Date())=10,10, iif(day(Date())=11,11, iif(day(Date())=12,12,
iif(day(Date())=13,13, Null))))))))))))) AS Adjustment
FROM tablename;

but as soon as I added the 14th iif(), I got the "Expression too complex in query expression ..." error.

On further testing, I was able to save your query with the first 13 iif() in "As Adjustment", but when I added the 14th iif(), the same error popped up.

So I think we can nest at most 13 iif() together, though I can't find any documentation on it.
-------------------------------
Since each three of the iif() in your query return the same value, you can combine their conditions to eliminate two of the iif().

For example, the following three iif() all return "1.05":-
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=1,"1.05",
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=2,"1.05",
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=3,"1.05",

They can be combined into one:-
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0120" And DatePart("q",[datedelivered]) IN (1,2,3),"1.05",

In this way, the 19 iif() can be reduced to 7.

Hope it helps.

 

[Pat Hartman]

Some time ago, didn't we suggest removing the IIf()'s and replacing them with a user defined function or creating a lookup table that you could join to. Both solutions would be far easier to maintain and understand then these awful nested IIf()'s.

 

[Jon K]

Yes, if reducing the number of iif() doesn't help, you should follow Pat's advice and create a function.

Thanks, Pat.

 

[DaniBoy]

Yes you did

I tried that but am not that good in code!! I could never even got the start on that. Do you have an example DB with that scenerio?

DaniBoy

 

[Pat Hartman]

Did you try either of the solutions offered to you last July?

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31742&highlight=0135