Kelly,
The problem most likely is ALLTRIM() in the WHERE/JOIN because it returns variable lenght strings. Try to run your query with SET ANSI ON. If it fixes the problem, you'll have to either run your query this way always or get rid of ALLTRIM()s.
See
http://fox.wikis.com/wc.dll?Wiki~IndexExpressionRequirements~VFP for details on proper index key expressions.
>Interestingly when it is ran in 8 it returns this
>
>level for A : none
>level for B : none
>join A&B : using temp index
>
>If this is the issue what changed from 8 to 9 and how do I fix it??
>
>here is the select statment in question:
>Select Member.mem_no, Member.company, Member.addr1, Member.addr2, Member.city, Member.st, Member.zip,Member.mail_addr1, Member.mail_addr2, Member.mail_city, Member.mail_st, Member.mail_zip,Member.next_call, Member.next_reas, Member.dba, Member.phonenum, Member.phonenum2, Member.faxnum,Member.stdate, Member.indate, Member.dea, Member.beds, Member.csr, Member.inactreasn,Member.deadate, Member.rejoin, Member.Type, Member.ccode,SPACE(25) As field1, Space(40) As field2, Space(40) As field3, Space(20) As field4,memcomments.commdate As field5, memcomments.rep As field6, memcomments.comtype As field7,memcomments.comments As field8, 'b' As linetype;
>From Member INNER Join memcomments On Alltrim(Member.mem_no)=Alltrim (memcomments.mem_no);
>Where Alltrim(memcomments.mem_no) = Alltrim('002191');
>ORDER By memcomments.commdate Desc ;
>INTO Cursor comtemp
--sb--