dBASE to Access .....again 1

[joell165]

Last week Rick helped me with a similar problem. I don't want to be a pest, but I need help once more.

In most of the downloads I get, the names are entered in the following format:

LAST FIRST (space no comma) in one field (name)

To make two fields out of the name field in dBASE I do the following:

1) I create two fields, lname and fname.

2) I then use the following code:

replace all lname with left(name,at (" ",name))
replace all fname with stuff (name,1,at(" ",name),"&quot


Could someone please translate that into SQL or something I can use to get the same results in an Access table?

Thanks for any and all help.

Joel

 

[scriverb]

Using your naming convention use the following SQL in a query. You will have to change the name of the table to your tables name.

UPDATE tblYourTableName SET tblYourTableName.lname = Mid$([tblYourTableName]![name],1,InStr(1,[tblYourTableName]![name]," &quot), tblYourTableName.fname = Trim(Mid$([tblYourTableName]![Name],InStr(1,[tblYourTableName]![Name]," &quot+1));

Hope this helps.

Bob Scriver

 

[joell165]

Thanks Bob, the code worked great. I appreciate your quick and accurate response.

joel