>>>>FYI -
>>>>SET DELETED ON was my problem...arrrggh!
>>>>
>>>
>>>Did you try my SQL and Sergey's? Do they return the same result? I think mine is essentially the same as Rich's.
>>
>>Yes, I tried both.
>>Sergey's worked, but it returned all the item_state ones, not just the two that I was looking for.
>>Your's had some strange syntax error I couldn't get passed, and I didn't really understand the part of the statement where the 'step1' was. However you DID point out the >=2 intead of = 2 in Rich's - so hey - everyone helped..haha... gotta love the VFP community!
>
>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)