Is there a Character limit on SQL View?

[DaniBoy]

Hello, I am trying to put some iif statements on my query expression, when I did it on design view it kept cutting off the code because of the 1024 character limit, I then went to SQL View and I was to type in more code. Today I need to type more code "iff statements" that actualy looks the same, all I need is to change it to onother value, but it tell me that I get a "Expression to complex in query expression" and displays code and whwn I say ok is blinking on the "=" . Here is the code:

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]="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;


ok this is the extra code I want to add before the "Null))))))))))))) AS Adjustment":

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"

I have checked everything and all seem to be good, infact I can run two seperate queries with the code. But I need it all in one...!!!

Thank you
DaniBoy

 

[The_Doc_Man]

See reply to reprhasing of this question.