>>I got the same syntax error. Could it be related to VFP 8 and your code is for VFP 9?
>>
>>I couldn't test it, but I think there's a flaw in your logic. Try testing it with 3 records in the items table for one order_no. All three should have state = 'CM'. I think you will generate a false positive because the count will be three, but both states won't be there.
>
>This works:
>
>SELECT Cms.order_no, Cms.order_st2, Cms.hold_type, Cms.hold_date,;
> Cms.ordertype, Items.item_id, Items.item_state, Items.needscan,;
> Items.packed;
> FROM ;
> cms Cms ;
> LEFT OUTER JOIN items Items ;
> ON Cms.order = Items.order;
> WHERE ( Cms.order_st2 = ( 'PI' );
> AND Items.item_state IN ('PI','CM') );
> AND Cms.order_no IN (SELECT cms2.order_no ;
>FROM cms cms2,items items2 ;
>WHERE cms2.order_no = items2.order_no AND ;
>cms2.order_st2 = "PI" AND ;
>items2.item_state in ('PI', 'CM') ;
>GROUP BY 1 HAVING COUNT(DISTINCT items2.item_state) >= 2)

Yes, I forgot to mention that my code is for VFP9 only, since we can not use projection in early versions.

Your code, Rich, is correct, because you use DISTINCT clause (big doh for my version).