change 1 field fullname to 2 fields first name and last name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to change one field "Fullname" and make 2 new fields "FirstName" and
"lastName"? How do I do this?
 
Try

FirstName: Left([Fullname], Instr([Fullname] , " ")-1)

LastName: Mid([Fullname], Instr([Fullname] , " ")+1)
 
I need to change one field "Fullname" and make 2 new fields "FirstName" and
"lastName"? How do I do this?
Click to expand...

With a couple of Update queries.

Add the two new fieldnames to your table. Assuming that Fullname
contains text like

Joe Doakes

you can run an Update query updating FirstName to

Left([Fullname], InStr([FullName], " ") - 1)

and LastName to

Trim(Mid([FullName], InStr([Fullname], " ")))

InStr will find the first blank in the name, and the Left() and Mid()
functions will extract the substrings to the left of, and starting
with, the blank.

Note that you might have names in your table such as

Billie Joe Jones
Hans van der Steen

These will go into the new fields as "Billy" firstname, "Joe Jones"
last name - which is incorrect; or "Hans" firstname, "van der Steen",
which is correct. I'd suggest running a query on the new table with a
criterion

LIKE "* *"

on LastName to find and manually correct all these instances. It's
almost impossible to automate this task!

John W. Vinson[MVP]
 
Back
Top