Update with a comma

[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?

 

[John Spencer]

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