Hi Terry,

Your SQL doesn't match what you are saying you're trying to do. Can you clarify your intentions?
Anyway, you should redefine begining and end of each period instead of complication query itself.

>Got this complex SQL. The field ET.Number is to be summed if it falls between the prev. start and end dates, or between the curr. start and end dates.
>
>That's how it stood and worked OK. BUT, sometimes the date ranges can be open-ended, eg there's a prev. period start date but "open-ended" end date. So had to extend the SQL to say, like, "if ther's no end to prev period make sure the date is before the start of the curr. period)
>
>Trouble is:
>"too many arguments" error. Can't figure where it's going wrong. Need another pair of eyes looking over my shoulder. Can anyone spot the error?
>
>'ppreciate it.
>
>

>SELECT ET.Operator, ET.Route, ET.Class, ET.Date, ET.RorF, op.Desc as OpDesc, ;
>       Thisform.lmGetServeType( ET.Operator, ET.Route, ET.Date) as ServeType, ;
>       IIF( ET.Route == ET.Mcl_Rte, "    ", ET.Mcl_Rte) as ContrNum, ET.Family, ;
>       SUM( IIF( BETWEEN( ET.date, ;
>	                 IIF( not EMPTY( ldStartCurr), ;
>		             ldStartCurr, ;
>	                      ldEndPrev), ;
>	                 ldEndCurr), ;
>	        ET.Number, ;
>	        0000000) ) AS CurrPass, ;
>       SUM( IIF( BETWEEN( ET.date, ;
>		        ldStartPrev, ;
>		        IIF( not EMPTY( ldEndPrev), ;
>			    ldEndPrev, ;
>			    ldStartCurr), ;
>	        ET.Number, ;
>	        0000000) ) AS PrevPass ;
>  FROM ETMData ET ;
>	JOIN Operator op ;
>	On   ET.Operator 	== op.Code ;
>  WHERE ET.Operator 	== lnOpCode ;
>  AND	(    ET.date BETWEEN ldStartCurr and ldEndCurr ;
>  	  OR ET.date BETWEEN ldStartPrev and ldEndPrev) ;
>  AND not DELETED() ;
>  ORDER BY ET.Operator, OpDesc, ET.Route, ET.Class ;
>  GROUP BY ET.Mcl_Rte, OpDesc, ET.Operator, ET.Route, ET.Class ;
>  INTO CURSOR csrETMPassengers NOFILTER READWRITE
>

>
>For ref. the last 2 SUM()s were as follows:
>
>* 30/08/07 SUM( IIF( BETWEEN( ET.date, ldStartCurr, ldEndCurr), ET.Number, 0000000)) AS CurrPass, ;
>* 30/08/07 SUM( IIF( BETWEEN( ET.date, ldStartPrev, ldEndPrev), ET.Number, 0000000)) AS PrevPass ;