>It looks like this was supposed to be fixed in SP1, "Indexes on STR() are penalized during Rushmore optimization if SET ANSI is OFF". Unfortunately, we're still seeing the same problem. We've got a 400MB table that has this index:
>

>STR(VAL(CUST_NUM), 8) + SYS(11, TRANS_DATE)
>

>Under VFP 8, this query is optimized:
>

>SELECT * ;
>  FROM custhist;
> WHERE STR(VAL(cust_num), 8)+SYS(11, TRANS_DATE) = "633621"
>

>Under VFP 9, even with the SP1, we don't get any optimization. Under VFP 8 we do.
>
>It's easy enough to reproduce, try these in both VFP 8 & VFP 9:
>

>SYS(3054, 11)
>CREATE TABLE c:\testopt (cust_num C(8), trans_date D)
>INDEX ON STR(VAL(CUST_NUM),8)+SYS(11,TRANS_DATE)  TAG custhist
>SELECT * ;
>  FROM testopt;
> WHERE STR(VAL(cust_num),8)+SYS(11,TRANS_DATE) = "  633621"
>

SYS(11) return a fixed lenght string C(7),
but VFP consider it of varying length ( another little bug ).

try this:

SYS(3054, 11)
SET ANSI OFF
CLEAR
CREATE TABLE c:\testopt (cust_num C(8), trans_date D)
INDEX ON STR(VAL(CUST_NUM),8)+ PADR(SYS(11,trans_date),7) TAG custhist
SELECT * ;
	INTO ARRAY x;
  FROM testopt;
 WHERE STR(VAL(cust_num),8)+ PADR(SYS(11,trans_date),7) = "  633621"