>I store a Customer Id in my table. I then created an index on that table that sorts the records by Customer Last Name. I use a UDF() in the database's Stored Procedures to retrieve the Last Name from the Customer table using the Customer Id stored in the table. The UDF() gets the Customer's Last Name by doing a SELECT SQL....
>
>Now I want to set the order of the table to Customer Last Name and perform a SEEK to find a specific record. e.g. SET ORDER TO Customer, SEEK('GUCWA') (The index is UPPER()). The SEEK does not consistently find the correct record. More often than not, it does not find any records at all. SET EXACT is 'OFF' and SET NEAR is 'ON.
>
>Is it possible to do what I am trying to do? Is there another method that will accomplish the same result? I am using VFP 5.0. I am using tables, not views, and it is too late to change to local views.
>
>Thanks,

Hi Annemarie:

Your description of what you want to do sounds okay to me. Maybe its the specific details that are the problem.

Are you really doing a seek on 'GUCWA' and not just a memvar? If a memvar, try SEEK(UPPER(jcmemvar)) and see if it works. Are you using the SEEK() function or SEEK command? If the function, then something like "IF SEEK('XXXX')" will return true only if its actually FOUND(). If using the SEEK command, then "SEEK ('XXXXX')" needs to check "IF FOUND()" -- again, only if it actually finds it. Do you know that the value being sought is actually there? (ie: if you browse the table after your seek)... All that SET EXACT and NEAR do for you is avoid going to EOF() if the value is not found. If you just want the closest match, maybe you already have it, but just aren't correctly understanding how to interpret the results.

HTH

Rob