Code to seperate first and last name

  • Thread starter Thread starter Lisa B.
  • Start date Start date
L

Lisa B.

Does anyone have a quick code to seperate a name field into two fields

I have a Name field with data that looks like this ==> John Smith
I would like to seperate this name into two fields FirstName and Last Name

I have another table with a name field and data that looks like this===>
Smith, John
I would also like to break this into two fields
 
You'll need to create an update query to populate your new fields, FirstName
and LastName.

For John Smith:

FirstName: Left([MyTable].[Name], InStr([MyTable].[Name], " ") -1)
LastName: Mid([MyTable].[Name], InStr([MyTable].[Name], " ") +1)

Note that the above will not return accurate FirstName and LastName data
where names might be like:

John Peter Williams
Jo Ann Smith

For Smith, John:

LastName: Left([MyTable].[Name], InStr([MyTable].[Name], ",") -1)
FirstName: Mid([MyTable].[Name], InStr([MyTable].[Name], ",") +2)
 
Lisa said:
Does anyone have a quick code to seperate a name field into two fields

I have a Name field with data that looks like this ==> John Smith
I would like to seperate this name into two fields FirstName and Last Name

I have another table with a name field and data that looks like this===>
Smith, John
I would also like to break this into two fields
Click to expand...

Now that you have some answers to your question, think about
what you're going to do with names like Mary Smith - Jones,
John von Neuman, J. Thomas Smythe, ad nauseum.
 
Marshall Barton said:
Now that you have some answers to your question, think about
what you're going to do with names like Mary Smith - Jones,
John von Neuman, J. Thomas Smythe, ad nauseum.
Click to expand...


Mr John Smith, John Smith III, ... :-)

I noticed that the reference site for my main application, had entered a
forname of "." (a dot) for a customer record. I asked them why they'd done
that. It turned out that the customer in question has changed his name,
legally, to a surname only! He no longer has a forname!!

TC
 
Back
Top