Update with a comma

  • Thread starter Thread starter Maarkr
  • Start date Start date
M

Maarkr

I've got thousands of records with this format.
I've parsed out most everything but I still have this problem:
Smith Robert A
Smith, Robert B

Any ideas for an update query so they all have commas after the last name?
 
You can come close, but will still have problems with names like the following
(just to give you a few examples)
De la Hoya Oscar
Mc Henry James
Spencer John, Jr
Williams, Jr John


UPDATE [Your Table]
SET [TheNameField] = Left([TheNameField],Instr(1,[TheNameField]," ") & "," &
Mid([TheNameField],Instr(1,[TheNameField]," ")
WHERE [TheNameField] Like "* *" AND [TheNameField] Not Like "*,*"



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top