Reindexing of table not working

[acorbally]

I have a unique problem and am unable to find a fix. I have found that I have a table that has a cdx associated with it that seems to be corrupt. When I try to set the order on the table, it is appearing to be a bad index. I see this because I am doing an set order to name and the index is called name and consists of: alltrim(pat_lstnam)+alltrim(pat_fstnam) what I see after I do a set order to name is when I seek 'MARTIN' it goes to the names Martin, but I will see three or four Martin's and then a MARTINELL and then right after that a 'Martin' again. Not right at all. I have tried to do a reindex and also dropped the cdx and recreated it, I still see the martin, martinell, martin scenerio. Any suggestions??

 

[acorbally]

I found my own problem. It was from creating an index with alltrim. If I removed the alltrim and just created an index with pat_lstnam+pat_fstnam it works just fine. Sorry for the confusion.

 

[DSummZZZ]

Just for future referenc, when you create an index using ALLTRIM(), Fox looks at the first record to match the criteria (i.e., 'MARTIN', which is 6 chars long) then bases the length of the key on the length of the ALLTRIM'ed value. So from there on out, Fox will olny 'see' the first 6 characters of the field, and index them on that.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[dbMark]

Yes, the ALLTRIM() function will returns strings of varying lengths and that will at the least give unexpected or unpredictable results. But it is okay to use expressions such as LTRIM(), RTRIM(), SUBSTR() and STR() since those substrings will be of consistent lengths.

And of course it is recommended that you should avoid creating an index which is based partly on memory variables or fields in other tables.