Something is wrong in your IIF(between statements)

IIF( BETWEEN( ET.date, ;
IIF( not EMPTY( ldStartCurr), ;
ldStartCurr, ;
ldEndPrev), ;
ldEndCurr),

It should be re-coded differently, give me some time to figure this out.

I think the simplest solution would be to use a placeholder for the empty date, e.g. date(1900,1,1) for example.

Another way would be to write this SQL as a string using text/endtext with different sum depending on conditions and then macro execute it.

Let me know if you need more help on either of these two ideas.

>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 ;