>>I am new to Foxpro(v5). I have been using Access for about 5 years. In Access I use an update query to split a 'Name' field into two other fields by looking for a comma or space between words in the 'Name' field. For example I will have Smith, John in the 'Name' field and I split the words into two separate blank fields (LastName, FirstName) buy having the Update query looking for the comma seperation.
>>
>>I am using Foxpro now since I have very large dbf files to update. I ran the query in Foxpro but cannot find the right expression to split one field into two others. It seems the expressions for Foxpro and Access are quite different.
>>
>>
>>In FoxPro I tried the following to created 2 seperate fields out of one;
>>
>>?fullname && "John Doe"
>>nSplitPosition = AT( space(1), fullname )
>>firstname = LEFT(fullname, nSplitPostition - 1)
>>?firstname && "John"
>>lastname = SUBSTR(fullname, nSplitPosition + 1)
>>?lastname && "Doe"
>>lastname = RIGHT(fullname, LEN(fullname) - nSplitPostion)
>>?lastname && "Doe"
>>
>>When I apply the update the first record in the Firstname field is split properly. However all the following records are not split by the space but rather by the same number of charcters that the first record was split. For example I get the following results:
>>
>>Fullname field contains Jim, Smith for the first record
>>Fullname field contains John, Brown for the second record
>>
>>After the Update I get the following in the firstname field:
>>
>>Record 1: Jim
>>Record 2: Joh (it is missing the "n")
>>
>>Any ideas?
>
>Try: (typo fixed in boldface)
>
>

REPLACE ALL Lastname WITH LTRIM(SUBST(Fullname,AT(',',Fullname)+1)<b>)</b>, ;
>               Firstname WITH LEFT(Fullname,AT(',',Fullname)-1)

>
>if you want to split at the comma; you can use the space, but the comma will get left behind if present, and it's reasonably likely that a last name might contain spaces. You need to evaluate the AT on a line by line basis, rather than compute it once and apply it for all values.