How do I search for a comma from the right so I can separate last name first name?

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I want to write a query that will separate a whole name into last name
first.

Ex: Smith,John = Smith John

How can I do this?

Thanks
 
Hi Dave

maybe i'm missing something but if you open the table up, select the column
& use edit / replace for a comma replace with a space in any part of the
field, won't that give you what you want?

cheers
JulieD
 
My fault - I did not ask that question well.

I want to take the Name Field ( Smith, John) and create two new fields Last
Name (Smith) First Name (John)

Sorry

Dave
 
IF you ALWAYS have a comma at the end of last name and don't have something like
Spencer, Jr, John
AND if you have the fields already constructed
THEN you can use an update query that looks something like the UNTESTED one that follows.

UPDATE [YourTable]
SET [LastName] = Left([FullName],Instr(1,[FullName],",")-1),
[FirstName] = Trim(Mid([FullName],Instr(1,[FullName],",")+1))
WHERE [FullName] Like "*,*"
 
Back
Top