I need to change one field "Fullname" and make 2 new fields "FirstName" and
"lastName"? How do I do this?
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]