>>Thanks Jim. I do have a question though. I have identical SELECT statements running prior to this one and after this one, without a NOFILTER designator; the only difference being that they select from different tables and they run absolutely OK. That is, RECCOUNT('cCursor') and _TALLY return the same number of records. These tables are also indexed. If the case is that VFP decides to optimize one statement and ignore the others, the same behaviour happens when running the SQL from the command prompt. It would interest you to know that if the table is copied to another with a different name, this problem does not occur. The table's name is 'bookbase'. I know its not a VFP reserved word. A reply would be very helpful.
>>
>Sanjay,
>
>The one thing that you can absolutely depend on about how SELECT will produce the result set is that you CANNOT depend on anything about how SELECT will produce the result set. The query optimizer in Visual Foxpro may run the same SELECT two different times and use different methods for getting the results. The optimizer is very smart about the environment it is running in, and uses the most efficient method of producing the result. To get the number of records in the result set ALWAYS use _TALLY and NEVER use RECCOUNT(). RECCOUNT() is not dependable with SQL SELECT results, as you have seen.
>
>If you need the SELECT to produce a new cursor that is not a filtered copy of the original table, then use the NOFILTER option of the SELECT command.
>
>PS. Sorry about the other message, it didn't seem like you had seen my messages.

Jim,

I seem to have stumbled upon the answer. It did not involve NOFILTER. In fact NOFILTER did not make any difference. I am not trying to equate RECCOUNT() with _TALLY as you seem to perceive. I really don't care about VFP optimization (in the program that is!) and am quite happy to have a filtered data set under normal conditions.

However, the problem was that VFP attached the parent's CDX() file to the resultant set i.e CDX(1, 'cCursor') returned the path to the parent's .CDX, as I have mentioned before, although the cursor has NO index at all! This does not happen with any other table. As a result, following this piece of code, I have a BEGIN TRANSACTION statement which always returned an error (1548), which is, that we cannot have an .IDX file open in a transaction. I know for a fact that there are no .IDX files attached to any of my tables. It seems that VFP is making a temporary .IDX file which points to the parent's CDX() (for optimization maybe) even though you have not asked for one, ONLY if one of the TAGs in the .CDX of the parent table is indexed on a raw DATE field! (DTOS(dDate)) is OK!!

I have checked this out with other tables and it seems consistent. The problem is sorted, but I am a bit confused. Please let me know if you find the same behaviour.

Thanks to you and to Bruce for all your help.

Sanjay