Split one column into two - first and last names

[slindesigns]

In my table, there are a couple thousand names in the database. The problem is, there is only one column for names and now would be a good time to seperate that into two columns (First, Last).

Can someone tell me how to do this, step-by-step? I'm a newbie Access user.

Thanks for your help.

 

[mph1]

The following should give you a good idea on how to proceed.

http://www.mvps.org/access/strings/str0001.htm

An investment in knowledge always pays the best dividends.
by Benjamin Franklin

 

[FancyPrairie]

Here's a quick little routine that should do it. Be sure to make a COPY of your database before running the code in case some names fail to fit the pattern. In that case, you will need to tweek the code. NOTE that this code assumes that the format of the names is FirstName LastName (space between first and last name). It also assumes that you have added 2 fields to your table named strFirstName as strLastName.

Function ConvertName()

Dim rst As ADODB.Recordset
Dim varFLName As Variant

Set rst = New ADODB.Recordset

rst.Open "Select * from YourTableName;", CurrentProject.Connection, adOpenKeyset, adLockPessimistic

While Not rst.EOF
varFLName = Split(rst!strFirstLastName, " &quot
rst!strFirstName = varFLName(0)
rst!strLastName = varFLName(1)
rst.Update
Wend

rst.Close

MsgBox "done"

End Function