>>>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?