Hi Fred,

That's a bummer.

Well two more things to consider:
1) (as others have said) your statement is rife with VFP reserved words. I know for sure that "date" and "type" are and have a strong suspicion that "number" may also be.
People have reported all kinds of odd results when keywords were involved as field names, especially in SQL statements. To me the existence of a subquery only increases the chance.

2) Maybe setting your subquery variables "Thisform.sdate.value" and "thisform.edate.value" to locals first, then using the locals in the query, will make a difference.

Good luck

JimN

>Good Thought. I have tested it but I get the same results.
>
>
>>Fred,
>>
>>Do you get a different result if you include RECEIPTS in the FROM clause?
>>
>>Just a thought.
>>
>>JimN
>>
>>>The subquery works perfectly as a stand alone query. When it is a subquery, than I get the error. I remove All of the Where clause and the subQuery will work.
>>>
>>>
>>>
>>>
>>>>Does your subquery work as a stand alone query?
>>>>If the following works the problem is not the word 'WHERE'
>>>>in your subquery.
>>>>Select number from receipts ;
>>>>where BETWEEN(letterdate,Thisform.sdate.value,thisform.edate.value) ;
>>>>and lettertype = [I]
>>>>>I removed the whereclause from the subquery and everything is fine. We've had other problems with reserved words as field names and aliasing them did fix the problem. Never let a DOS Clipper programmer do your data design.
>>>>>
>>>>>I started cutting the subquery down, one phrase of where clause at a time until I got the sub query to work. I even removed them in every possible order (ther were only 2). The subquery would execute when the where clause rmoved.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>I would suggest aliasing your tables. If you use table aliases you can avoid the reserved word problem. Also I think that the distinction between the number field in the main query and the number field in the subquery is unclear.
>>>>>>By aliasing the incoming and receipts tables you can reference the fields by table.
>>>>>>
>>>>>>>This is absolutely not the case. I use WHERE clauses in subqueries all the time. I'm betting the use of number,date and type as field names and as the ORDER BY criterion is what's is causing the trouble. These are all reserved words in VFP.
>>>>>>>
>>>>>>>>Okay, being stupid, appears that a where clause is invalid in a subquery.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>In VFP 5.0 I seem to be having a problem with a subquery. I can run the subquery stand alone and it works fine. Please someone tell me what I'm not seeing.
>>>>>>>>>
>>>>>>>>>****
>>>>>>>>>
>>>>>>>>>SELECT uniqueid,number,date,type,sn,payee1,document from incoming ;
>>>>>>>>> where BETWEEN(date,Thisform.sdate.value,thisform.edate.value) ;
>>>>>>>>> and number NOT IN (Select number from receipts ;
>>>>>>>>> where BETWEEN(letterdate,Thisform.sdate.value,thisform.edate.value) ;
>>>>>>>>> and lettertype = [I]) ;
>>>>>>>>> order by number ;
>>>>>>>>> into cursor curLetter NOFILTER NOCONSOLE
>>>>>>>>>
>>>>>>>>>*****
>>>>>>>>>
>>>>>>>>>Ideas?