Separating Last Name, First Name into Two Fields

[Scott]

Hello,

I have a single field in a table entitled "Name" that follows the format
"Last Name, First Name." I'd like a way to separate this data into two
separate fields; the first field would be "Last Name" and the second "First
Name" with no comma. 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.

 

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

 

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

 

[Dale Fye]

Sometimes I just hate this web based news reader. I got 4 errors stating my
post had been rejected, then they all show up.

--
Dale

email address is invalid
Please reply to newsgroup only.

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.

Hello,

I have a single field in a table entitled "Name" that follows the format
"Last Name, First Name." I'd like a way to separate this data into two
separate fields; the first field would be "Last Name" and the second "First
Name" with no comma. Any ideas?

I appreciate your help.

Scott