Hi chaps

This is one of those look-over-my-shoulder jobs:

I added the complex nested IIF() to this SQL. Intellisense matches up all my parenth. and AFAICS the IIF()s are pukka, but the compiler keeps screaming "Function Name is missing) err mess" every time I try to leave the method.

Maybe the SQL has given up on it?

Can anyone see the flaw, cos I can't see the brackets for the parenth any more?

Essentially it's (should be) saying "If there is a matching operator/route combo, in the RteFam table, well if it's dates are current with the ET rec's date, use its SERVTYPE field - else put a '#' (to denote out-of-date). If there is no match, put a '?' to denote 'not known'"

Have that on my desk by the (my) tommorrow morning would you? :-)

'ppreciate it

Terry

SELECT DISTINCT ET.Operator, ET.Route, ET.Class, ET.Date, ET.RorF, op.Desc as OpDesc, ;
	IIF( 	ET.Route 	   == RteFam.ERoute ;
	     AND ET.Operator == RteFam.Operator, ;
	     IIF( ((   ldStartCurr BETWEEN RteFam.Start_date AND RteFam.End_date) ;
	  	    OR (	ldEndCurr   BETWEEN RteFam.Start_date AND RteFam.End_date) ;
  		    OR (	RteFam.Start_date < ldStartCurr AND RteFam.End_date > ldEndCurr) ;
  		    OR (	RteFam.Start_date => ldStartCurr AND RteFam.End_date <= ldEndCurr) ; 		  
	  	    OR (	ldStartPrev BETWEEN RteFam.Start_date AND RteFam.End_date) ;
  		    OR (	ldEndPrev   BETWEEN RteFam.Start_date AND RteFam.End_date) ;
  		    OR (	RteFam.Start_date < ldStartPrev AND RteFam.End_date > ldEndPrev) ; 
  		    OR (	RteFam.Start_date => ldStartPrev AND RteFam.End_date <= m.ldEndPrev) ), ;
  		  RteFam.ServType, "#"), ;
  	     "?") as ServeType, ;	
	IIF( ET.Route == ET.Mcl_Rte, "    ", ET.Mcl_Rte) as ContrNum, ET.Family, ;
	SUM( IIF( BETWEEN( ET.date, ldStartCurr, ldEndCurr), ET.Number, 0000000)) AS CurrPass, ;
	SUM( IIF( BETWEEN( ET.date, ldStartPrev, ldEndPrev), ET.Number, 0000000)) AS PrevPass ;
  FROM ETMData ET ;
	JOIN Operator op ;
		On ET.Operator == op.Code ;
	LEFT JOIN RteFam ;
		On ET.Operator == RF1.Operator ;
        and (	ET.Route	== RF1.ERoute ;
      	     or EMPTY( RF1.ERoute) ) ;
  WHERE (	    ( ET.date BETWEEN ldStartCurr and ldEndCurr) ;
          OR ( ET.date BETWEEN ldStartPrev and ldEndPrev) ) ;
  AND not DELETED() ;
  GROUP BY	ET.Mcl_Rte, ET.Operator, ET.Route, ET.Class ;
  ORDER BY	ET.Operator, OpDesc, ET.Route, ET.Class ;
  INTO CURSOR csrETMPassengers NOFILTER