Remove middle name from field

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I need to change a database that has first middle & last
name in the same field. I would like to change it to
remove the middle name end up with the initial. The end
result is a field with first, middle initial, last name.
Any suggestions or help in doing that would be
appreciated.
 
If every record matched the format
<firstname><space><middlename><space><lastname> the following update query
would work. (Assumes Access 2000 or later, as it uses the InStrRev()
function first introduced in Access 2000.)

UPDATE tblTest SET tblTest.FullName = Left$([FullName],InStr([FullName],"
")-1) & " " & Mid$([FullName],InStr([FullName]," ")+1,1) & " " &
Mid$([FullName],InStrRev([FullName]," ")+1);

In reality, in any significantly large volume of data, there are almost
certain to be exceptions to the rule - no middle name, two part first,
middle, or last names, inconsistent punctuation, etc.

As always when making bulk changes to data, it would be wise to ensure that
you have a reliable, recent backup copy first.
 
Back
Top