Separating Last Name, First Name into Two Fields

[Scott]

Hello,

I have a table with a single field entitled "Name" that follows the format
"Last Name, First Name." I'd like a way to separate the data so that I have
one field with "Last Name" and another field with "First Name" with no comma
in either field.

Any ideas?

I appreciate your help.

Scott

 

[Dale Fye]

Create a query that looks like:

SELECT Left([Name], instr([Name], ",") - 1) as LastName,
Mid([Name], instr([Name], ", ") + 1) as FirstName
FROM yourTable
WHERE instr([Name], ",") > 0
AND instr([Name], ",") < LEN([Name])

Once you have this working, then add the two fields to your query, and
modify it to:

UPDATE yourTable
SET LastName = Left([Name], instr([Name], ",") - 1),
FirstName = Mid([Name], instr([Name], ", ") + 1)
WHERE instr([Name], ",") > 0
AND instr([Name], ",") < LEN([Name])


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.