>>>>>Hi everybody,
>>>>>
>>>>>I've created a local view in View Designer and modified it in EView. I modified SQL to be
>>>>>

>>>>>SELECT rcode, town, record_fm,  record_to, landct_fm, landct_to,  ;
>>>>>calmnth, mdflag, nodata,  notes, ddflag, dsid, state,  extryear, extrweek, ;
>>>>>ccode FROM support!filingdate ;
>>>>>WHERE BETWEEN(dsID , ?lnIdLow, ?lnIDHigh)  ;
>>>>> AND between(state+extryear+extrweek, ?lcStWkLow,  ?lcStWkHIgh) ;
>>>>>ORDER BY extryear DESC, extrweek DESC,  rcode, town

>>>>>
>>>>>On the SQL showplan I see
>>>>>Using index tag Deleted to rushmore optimize table filingdate
>>>>>Rushmore optimization level for table filingdate: partial
>>>>>
>>>>>I have both indexes on DsID and StateWeek in FilingDate table. Do you know, what's the problem?
>>>>>
>>>>>Thanks in advance.
>>>>
>>>>Index stateweek is state+extryear+extrweek ?
>>>>
>>>>- Keith
>>>
>>>Yes. BTW, it doesn't show usage of the other index DsID too. May be this Show Plan just doesn't always work correctly...
>>
>>Ahah! VFP is tricky here and I wouldn't believe it if I didn't see it with my own eyes:
>>
>>"You can use BETWEEN() or INLIST() in the following two forms:
>>
>>eIndex BETWEEN(eIndex, eExpr, eExpr)"
>>
>>Looks like you have to change your WHERE to:
>>

>>dsID BETWEEN(dsID , ?lnIdLow, ?lnIDHigh)  ;
>>AND state+extryear+extrweek between(state+extryear+extrweek, ?lcStWkLow,  ?lcStWkHIgh)
>>

>>
>>Crazy, huh?
>
>Keith,
>
>Originally I had this SQL with BETWEEN as a command. I changed it myself to use BETWEEN as function (view designer creates COMMAND like SQL, I prefer to use FUNCTION type). In both cases, it doesn't make a difference. I guess, I just would not trust this show plan info. Obviously all three indexes DELETED(), StateWeek, DsID must be used. I don't see a reason, why they don't.

I think you missed my point. It seems VFP needs to see the Index expression as the first part of each component of the WHERE clause. It's like a mix of SQL BETWEEN and function BETWEEN(). Give it a try with DsID immediately before the BETWEEN() function and inside the parentheses.