PAT, I'm dying here!!!! help!!!!

[DaniBoy]

Hello Pat,
I think its great that you are writting a book!!! something else you could do is a reference booklet that explains all the event procedures on access. i.e. like what is "on form load", "on no data" "on dirty" ect..... and give examples. I think anyone would by that in a heartbeat, I would be the first!!! anyway here is my dilema, I have a query where I creadted and expression that is very long for the desing view, I went to SQL view and kept typing the code "since its all the same wording but dif values. i.e.
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0120" And DatePart("q",[datedelivered])=1,"1.05") I follow with the samething but dif values. Then I get to a place where it gives me this error " Expression to complex for query " I checked all the syntax but all is fine!!! here is the complete code so you can cut and paste and see what could be wrong!!!

SELECT WorkOrders.WorkorderID, Addresses.[Parcel#], WorkOrders.NewCheckBox, WorkOrders.DateDelivered, WorkOrders.FiscalYear, [Fname] & " " & [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]="0120" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=1,"0.96",
IIf([AP_DATA1_SPCD1]="0120" And [newcartsizecode]="0164" And DatePart("q",[datedelivered])=1,"0.91",
IIf([AP_DATA1_SPCD1]="0120" And [newcartsizecode]="0196" And DatePart("q",[datedelivered])=1,"0.88",
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0164" And DatePart("q",[datedelivered])=1,"0.95",
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0196" And DatePart("q",[datedelivered])=1,"0.91",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0196" And DatePart("q",[datedelivered])=1,"0.95",
IIf([AP_DATA1_SPCD1]="0120" And [newcartsizecode]="0135" And DatePart("q",[datedelivered])=2,"0.91",
IIf([AP_DATA1_SPCD1]="0120" And [newcartsizecode]="0164" And DatePart("q",[datedelivered])=2,"0.82",
IIf([AP_DATA1_SPCD1]="0120" And [newcartsizecode]="0196" And DatePart("q",[datedelivered])=2,"0.76",
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0164" And DatePart("q",[datedelivered])=2,"0.89",
IIf([AP_DATA1_SPCD1]="0135" And [newcartsizecode]="0196" And DatePart("q",[datedelivered])=2,"0.82",
IIf([AP_DATA1_SPCD1]="0164" And [newcartsizecode]="0196" And DatePart("q",[datedelivered])=2,"0.91",
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",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;

Thank you
DaniBoy

 

[Pat Hartman]

Two people provided excellent solutions:

The_Doc_Man
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31563

and

Raskew
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31550

Before I take the time to offer a third, what was wrong with those? The help you get here will be much better if you simply stick to a single thread for the same problem.