separtate first and last name in one column

  • Thread starter Thread starter Danny Boukhris
  • Start date Start date
D

Danny Boukhris

I have JOHN, SMITH in one column, but I need John in one
column and Smith in another column. Do I have to create a
column named Last name and copy and paste all the names?
There's gotta be an easier way.

Thanks.
 
Danny

A typical approach in this situation is to create TWO new fields (LastName
and FirstName), and use update queries to parse the simple names into those
fields.

I say "simple" because I don't know how consistent your "FullName" data is.
For example, do you have any folks with names entered like:

Smith, John
Smith, Jr., John
Smith y Garcia, John J.
Smith, Esq., J.J.
van de Smith, Jonathan
etc.

Building a "simple" query to find the position of the comma (?!there IS a
comma in every one, right?!) and breaking it there is easy, using the
Left(), Mid(), and InStr() functions. But the second and fourth examples I
gave have TWO commas -- these and other un-simple names may need manual
conversion.

Do the simple ones first, using update queries, then go back through and fix
all the ones that Access did wrong.
 
Thanks for info, but I still was unable to do it.
Here's what I did. I made update query and typed in
Left([UserName]) but got error message -wrong number of
arguments.

Can you please provide me with exact code, using JOHN,
SMITH. for example once I get the update query how do I
write the code for LEFT()

My table first just had NAME column containing JOHN, SMITH
I created two other columns - first and last. Then I went
to update query - That's where I'm stuck. -

Thanks!!
 
Danny

Based on your reply, it sounds like you expected the function to work
without any syntax. Have you looked at Access HELP for the specific syntax
you need for those functions?
 
Thanks for info, but I still was unable to do it.
Here's what I did. I made update query and typed in
Left([UserName]) but got error message -wrong number of
arguments.

Can you please provide me with exact code, using JOHN,
SMITH. for example once I get the update query how do I
write the code for LEFT()

My table first just had NAME column containing JOHN, SMITH
I created two other columns - first and last. Then I went
to update query - That's where I'm stuck. -
Click to expand...

If the comma is reliable:

update FirstName to

Left([UserName], InStr([UserName], ",") - 1)

and LastName to

Trim(Mid([UserName], InStr([UserName], ",") + 1)

Double check - FIRST, LAST would be rather unusual! Are you certain
it's "DANNY, BOUKHRIS" or is it "BOUKHRIS, DANNY"?
 
Back
Top