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

[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

 

[JulieD]

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

 

[Dave]

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

 

[John Spencer (MVP)]

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 "*,*"