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?

Thanks