We speak about VFP :o)) I am pretty sure Terry use Date type fields not DateTime.

>>Hi Searchers
>>
>>The below SQL is certainly not getting me the results I expect and, worse still gives me 1 spurious result.
>>
>>I've already done a search to multiply the number of journies done on eligible bus services, by a certain factor, to give a "trips" total for each bus operator, route no + (ticket) class, et al, within 2 date ranges.
>>
>>Now I wish to list all the journies that didn't get included in the prev. report because although the (bus) operator had a (ticket) CLASS, the class didn't have an associated MULTIPLIER in the OpClass Table.
>>

>>OpClass Table is as follows:
>>
>>Operator#, Class#, Multiplier, ...
>>
>>And Operator:
>>
>>Operator#, Class#, ...
>>

>>
>>The ETMDATA table holds details of all Electronic Ticket Machine (ETM) transactions over a period.
>>The reason why OpClass needs the 2 keys is that a class is not unique to an operator, and certain class nos mean different things to different operators, but are issued by the ETM manufacturer.
>>
>>Have I got the NOT EXISTS logic wrong or what is it?
>>
>>'ppreciate it.
>>
>>Terry
>>
>>

>>Select	DISTINCT ET.Operator as MLOperator, ET.Class as MLClass, ;
>>		op.Desc as MLOpDesc, rf.ServType as MLServType, ;
>>		SUM( IIF( BETWEEN( ET.date, ldStartCurr, ldEndCurr), ET.Number, 0000000)) AS MLCurrPass, ;
>>		SUM( IIF( BETWEEN( ET.date, ldStartPrev, ldEndPrev), ET.Number, 0000000)) AS MLPrevPass ;
>>  FROM ETMData ET ;
>>	JOIN Operator op ;
>>		On ET.Operator 	== op.Code ;
>>	JOIN RteFam rf ;
>>		On ET.Operator 	== rf.Operator ;
>>		and ET.Route    	== rf.ERoute ;
>>  WHERE  ET.date BETWEEN ldStartCurr and ldEndCurr ;
>>  OR 	ET.date BETWEEN ldStartPrev and ldEndPrev ;
>>  AND 	NOT EXISTS ;
>>  	( Select * from OpClass ;
>>  		WHERE OpClass.Operator == ET.Operator ;
>> 		AND   OpClass.Class    == ET.Class ) ;
>>  ORDER BY	ET.Operator, ET.Class ;
>>  INTO Cursor csrMultless
>>
>>

>
>Terry,
>
>I have sure of issue but I do know that you should probably rethink using BETWEEN when doing SQL Datetime queries comparison
>
>Declare @ETdate datetime
>declare @ldStartCurr datetime
>declare @lEndPrev datetime
>
>set @ETdate = '07/20/2006 10:00:00'
>set @ldStartCurr='07/20/2006 08:00:00'
>set @lEndPrev = '07/20/2006 9:55:00'
>
>if (@ETdate between @ldStartCurr and @lEndPrev)
>begin
> print 'yes'
>end
>else
> print 'no'
>
>ET.Date would not be considered between ldStartCurr and lEndPrev because is may fall in the date range but it does not fall in range when time is evaluated.
>
>This is just a guess...
>
>Carl