Separating Last Name, First Name into Two Fields

  • Thread starter Thread starter Scott
  • Start date Start date
S

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



Dale Fye said:
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.



Scott said:
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
Click to expand...
Click to expand...
 
Back
Top