>>If, by that, you mean the date tests, would that I could. I DID have them simpler (in another part of this form) but later found I needed all these tests, otherwise hits got through that shouldn't have, or didn't where they should.
>>
>>Anyway, whether it's complex or not (and even slow), that's not the issue here. Sergey spotted the wrong type of BETWEEN.
>>
>>>Can you simplify your dates manipulations? They look too complex to me...
>>>
>>...
>
>I found it too, but I think this complex condition has to be re-written. Please post the definition of what you're trying to achive with these dates, then someone may come with a better way of doing it.

I agree - it's taking far too long now. Below is how it was before I added the IFF( IFF( etc. ...

As I said in the 1st post, it's checking for the approp. RteFam rec for eac ET rec's date.
A RteFAm (route family) rec has dates of currency. Sometimes and ET rec can be before, during or after this date. If there's no current RteFam rec, for an Oerator/Route combo then # is put in the output; if there is no rec at all (current or not) to match trhen "?" us the result.

I've just realised, The RteFam recs may have no start/stop dates, meaning they're "infinite". They may have an end date but no start date, or vice versa, in which case they're current any time up to the end date, or any time after the start date, respectively. Hmmm. I must smoke on um some more. :-)

Wait WINDOW "ETM Data: Finding Journeys within the Time periods ..." NOWAIT NOCLEAR 
* For Now>	SELECT DISTINCT ET.Operator, ET.Route, ET.Class, ET.Date, ET.RorF, op.Desc as OpDesc, ;
* For Now>		IIF( ET.Route == RF1.ERoute, RF1.ServType, "?") as ServeType, ;	
* For Now>		IIF( ET.Route == ET.Mcl_Rte, "    ", ET.Mcl_Rte) as ContrNum, ET.Family, ;
* For Now>		SUM( IIF( BETWEEN( ET.date, ldStartCurr, ldEndCurr), ET.Number, 0000000)) AS CurrPass, ;
* For Now>		SUM( IIF( BETWEEN( ET.date, ldStartPrev, ldEndPrev), ET.Number, 0000000)) AS PrevPass ;
* For Now>	  FROM ETMData ET ;
* For Now>		JOIN Operator op ;
* For Now>			On 	ET.Operator 	== op.Code ;
* For Now>		LEFT JOIN RF1 ;
* For Now>			On	ET.Operator 	== RF1.Operator ;
* For Now>	        and (	ET.Route	== RF1.ERoute ;
* For Now>	      	     or EMPTY( RF1.ERoute) ) ;
* For Now>	  WHERE (	( ET.date BETWEEN ldStartCurr and ldEndCurr) ;
* For Now>	  		 OR ( ET.date BETWEEN ldStartPrev and ldEndPrev) ) ;
* For Now>	  AND not DELETED() ;
* For Now>	  GROUP BY	ET.Mcl_Rte, ET.Operator, ET.Route, ET.Class ;
* For Now>	  ORDER BY	ET.Operator, OpDesc, ET.Route, ET.Class ;
* For Now>	  INTO CURSOR csrETMPassengers NOFILTER 

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( ((   BETWEEN( ldStartCurr, RteFam.Start_date, RteFam.End_date) ;
	  		    OR BETWEEN( ldEndCurr, RteFam.Start_date , RteFam.End_date) ;
  			    OR ( RteFam.Start_date < ldStartCurr AND RteFam.End_date > ldEndCurr) ;
  			    OR ( RteFam.Start_date => ldStartCurr AND RteFam.End_date <= ldEndCurr) ; 		  		    OR BETWEEN( ldStartPrev, RteFam.Start_date, RteFam.End_date) ;
  			    OR BETWEEN( ldEndPrev, RteFam.Start_date, 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 

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'"