Hi Bruce,
>I tested last week with an index on Deleted(), but have never used COLLATE. The current setting is MACHINE. Does that affect it?
VFP ignores all indexes that doesn't match the current collate setting, ie IDXCOLLATE()#SET("COLLATE"). This might cause a previously optimizable query to become unoptimizable with the result that instead of a filtered table, a physical cursor is created.
>At this moment, I'm getting all filtered tables even with no index on Deleted(), and SET DELE ON, after trying several times. This morning and last week, I wasn't, as I say, it seems to be unpredictable...
What version of VFP are you using? VFP 5 has a bug here. Even with SET DELETED ON and no index on DELETED(), a filtered table is returned. _TALLY contains the total number of records, while COUNT would return only the number of non-deleted records. Even SELECT COUNT(*) would return the wrong number. This bug has been fixed in VFP 6. I think there was a tip about this a long tim back in FoxPro Advisor.
Christof
--
Christof