>Thanks Terry and Cetin for your replies.
>
>That does not produce what I want, and has a curious side effect into the bargain. Perhaps I did not explain clearly enough what I was trying to achieve.
>
>My previous example was hypothetical. Giving you a fuller version syntax of my original VFP 6.0 code:
>
>

>select membfilt.memb_no, sharx.last_pay, NVL(loans.last_loan, CTOD("")) AS last_loan ;
>  from membfilt join sharx on membfilt.memb_no = sharx.memb_no ;
>  left outer join loans on membfilt.memb_no = loans.memb_no INTO CURSOR lapsed ;
>  where loans.last_loan <= dlDate ;
>  ORDER BY 1
>

>
>produced 2912 records and the result set was correct.
>
>In VFP 8 SP1 exactly the same code produces 925 records, having lost all the records from membfilt/sharx which don't have a corresponding loans record.
>
>If I swap the 'where' for an 'and' as you both suggest:
>
>

>select membfilt.memb_no, sharx.last_pay, NVL(loans.last_loan, CTOD("")) AS last_loan ;
>  from membfilt join sharx on membfilt.memb_no = sharx.memb_no ;
>  left outer join loans on membfilt.memb_no = loans.memb_no ;
>  AND loans.last_loan <= dlDate ;
>  INTO CURSOR lapsed ;
>  ORDER BY 1
>

>
>I get 4626 records and some of the last_loan values are blank dates, when in the original loans file they are dates later than the value in variable dlDate. So 1) it produces an incorrect value in that field and 2) they shouldn't be in the results set anyway.
>
>In VFP 8.0 I have had to split the SQL into 2 commands, which works fine and produces the correct result set of 2912 records.
>
>

>select membfilt.memb_no, sharx.last_pay, NVL(loans.last_loan, CTOD("")) AS last_loan ;
>  from membfilt join sharx on membfilt.memb_no = sharx.memb_no ;
>  left outer join loans on membfilt.memb_no = loans.memb_no ;
>  INTO CURSOR allsharx
>select * FROM allsharx INTO CURSOR lapsed WHERE last_loan <= dlDate ORDER BY 1
>

>
>Is there a way to do this in one SQL command?

Now I think I see what you mean. What you say as a correct result set was in fact a 'correct' result set for your needs. You were after records:

Which either do not exist in loans or if existed last_loan < = dlDate (all of those who haven't loaned recently or never had a loan). IOW this set drops member_no's which exist in loans with a date later than dlDate.

select membfilt.memb_no, sharx.last_pay, NVL(loans.last_loan, {}) AS last_loan ;
  from membfilt join sharx on membfilt.memb_no = sharx.memb_no ;
  left outer join loans on membfilt.memb_no = loans.memb_no  ;
  WHERE NVL(loans.last_loan,{}) <= dlDate ;
  ORDER BY 1 ;
  into cursor lapsed

Cetin