>>The one exception that comes to mind is a Last name, first name lookup where I
have used TRIM(LNAME)+", "+FNAME as the key. I guess, in this case, it doesn't matter that there are trailing spaces added by VFP in the index.
>
>Hi Larry,
>
>Wouldn't this return a variable length string? What happens if your first key is
>"Moore, Demi " and your second key is
>"Schwartzenegger, Arnold "
>
>My guess that in order to pull this off you'd have to use something like:
>
>padr( trim(lname) + ", " + fname, 50)
>
>assuring constant key length through the table.
>
>Did I miss anything?
>
>Alex
Hi Alex,
That's sure one to have constant length. Another shorter one could be (provided I could live w/o an extra space after comma) :
lname-","-fname
that moves the trailing spaces to end :) I then shouldn't need to know fsize().
Alan you seem to be confused :
As Perry said you can't have variable length indexes. AFAIK bytes 12-13 in index header keep key length. It should be fixed for a key. I don't have an idea how would fox index with trim() and first entry had only one char (I think it would be ignored and field size used).
In order to find "Basoz,Cetin" when fname = "Cetin " and lname = "Basoz " you don't need to supply "Basoz,Cetin " in seek. "Set exact" effects it when not SQL and "set ansi" when SQL. With their default settings, if your key is lname-","-fname and supposing you have entries like :
Bacon-Smith
Basoz-Efecan
Basoz-Efecanlongerlastname
Basoz-Cetin
seek("Basoz,Efecan") && Finds kid :)
seek("Basoz") && Finds me :)
seek("B") && Finds Bacon-Smith
are all valid searches.
In an expression you could just match to the original key w/o setting exact on (where you really want to check exactly "Bacon-Smith" but not "Bacon-Smither") :
lcFirst = "Bacon"
lcLast = "Smith"
seek(padr(lcLast-","-lcFirst, ;
fsize("lname","myTable")+fsize("fname","myTable")+1) && +1 for comma
and in locate for or alike you use == operator :
locate for lname-","-fname == lcLast-","-lcFirst
select * from myTable where lname-","-fname == lcLast-","-lcFirst
It does the job for you and pads with spaces to fit sizes. If SQL was dealing with a connection between 2 tables then "set ansi on" would be needed to do an exact match (generally though I recommend also having separate indexes too on those fields).
Cetin