Splitting Fields in Tables

[Matthew1]

Hi I am trying to split a field in my table. My first and last names are together in one field, and I want them separated. How can i do this automatically? I ahve several thousand records, so i dont want to do it manually. ISnt there some way to separate it by spaces in the field? Thanks, Matt

 

[GumbyD]

Matt -

Add the new fields FirstName and LastName to your table then create an update query. I am assuming that the names are currently "First Last" No middle initials or names. To get the first name use:
Left([NameField],InStr([NameField]," ")-1)

To get the Last Name use:
Right([NameField],Len([NameField])-InStr([NameField]," "))

Good luck

GumbyD

 

[Matthew1]

explanation

Thanks for your reply. however, I am not an avid access user. Do you mind going into a little more detail? Where do you use the syntax that you gave me? Where do I do an updtae query? Thanks again! matt

 

[GumbyD]

Matt -

First and the new fields, FirstName and LastName, to your table (I will use Table1 as my table name). Create a new query and bring in Table1 from the show table window. Close the show table window. From the box in the upper part of the screen (that has table1 in the heading) find your FirstName field and double click on it - that will make it show up in the grid below. Do the same thing with the LastName field. Go up to "Query" on the menu bar and select update query. That will change the grid a bit as you will now have an Update To row. Under the FirstName field in the Update To row put:
Left([NameField],InStr([NameField]," ")-1)
Under the LastName field in the Update To row put:
Right([NameField],Len([NameField])-InStr([NameField]," "))

Now to run the query press the Red Exclamation Point on the tool bar.

Just a note make sure you change the name of the "NameField" in the expressions above to the name of your full name field.

If you have middle initials, middle names, names with more than one space in them - THIS WILL CAUSE PROBLEMS - use caution before you delete the full name field make sure an check the data.

GumbyD