How can I parse last name from string

D

Dave

How can I parse the last name (Jones) from a string that might be Mr. and
Mrs. Bill Jones or Mr. Bill Jones or Bill Jones?
 
J

John W. Vinson

How can I parse the last name (Jones) from a string that might be Mr. and
Mrs. Bill Jones or Mr. Bill Jones or Bill Jones?
Click to expand...

Depends on your version of Access. With 2003 (and perhaps 2002) you can use
the InStrRev (in string reverse) function to find the position of the last
blank:

Mid([Namefield], InStrRev([namefield], " ") + 1)

This will give incorrect results for names like "Hans van der Steen" or "Ramon
de Garcia", whose last names are van der Steen and de Garcia. Such names will
require a USB (Using Someone's Brain) interface.
 
Top