Hello all, my question is regarding which function is
used to separate first & last name in the same field. I
would like to have the last name in its own new field.
I'm not sure which function is needed to do this.
An Update query will let you do this. If you have a field Fullname
containing names like "John Vinson" you can add Firstname and Surname
fields; run an Update query updating Firstname to
Left([Fullname], InStr([Fullname], " ") - 1)
and Surname to
Mid([Fullname], InStr([Fullname], " ") + 1)
InStr is a function that finds the location of the first blank (in
this case) in a string; the Left() and Mid() functions extract
substrings based on that position.
You will then want to create a Query on the table with a criterion of
LIKE "* *"
on Surname. This will find cases where Fullname has three parts - e.g.
John W. Vinson <<< surname is Vinson but it will be W. Vinson
Ralph von Wau Wau <<< this will be von Wau Wau, and correct
Linda Lou Jones <<< her first name is Linda Lou, just ask her
Linda Smith Jones <<< her last name is Smith Jones, just ask HER
You'll need to manually fix these up, or get some VERY sophisticated
VBA code.
