How to split Full Name field into First Name, Last

Question

I have a table with a field called PATRN NAME which is set up with First_Name, Last_Name M.I.

Examples:

Smith, James M

Jones, Chris J.

Anderson, Wendy L

How can I break up this field into 3 different fields called FIRST_NAME, LAST_NAME, and MI ? I tried running a query LAST_NAME: [PATRN NAME]" & ", " to get just the last name but it didn't get any results. I didn't design this table by the way so I realize it wasn't smart to make a full field name without individual field names; I'm just in charge of fixing it.

Answer 1

Consider whether you can bend the Split Function to your will.

Here is an example Immediate window session.

PATRN_NAME = "Smith, James M"
? PATRN_NAME
Smith, James M
? Split(PATRN_NAME, ",")(0)
Smith
? Trim(Split(PATRN_NAME, ",")(1))
James M
? Split(Trim(Split(PATRN_NAME, ",")(1)), " ")(0)
James
? Split(Trim(Split(PATRN_NAME, ",")(1)), " ")(1)
M

You can't use Split() in a query directly. However you could build one or more user-defined functions and call the UDF(s) from a query.

That approach could make for a simpler query than one which requires a combination of other functions: InStr(), Mid(), Right(), etc. However, a UDF means the query can only work from within an Access application session; if you need a query which runs from outside Access (.Net, VBScript, PHP, etc.), the UDF will not be available.

I suggest you clarify whether your intention is to extract FIRST_NAME, LAST_NAME, and MI every time you query the data, or whether you will store those values separately in the table after you extract them once. That should influence the approach you choose. If you will split them out once and store, you could use a VBA procedure instead of a query.

Also decide what should happen with the optional dot after the middle initial, "Jones, Chris J.". Keep it in MI or discard it?

Answer 2

Try this:

UPDATE Tbl_Master_Working SET Tbl_Master_Working.[Last Name] = Mid([NAME],1,InStr([NAME],',')-1), Tbl_Master_Working.[First Name] = Mid([NAME],InStr([NAME],',')+2);

Answer 3

Try this:

For last name: LAST_NAME: Mid(PATRN_NAME,1,InStr(PATRN_NAME,',')-1)

For first name: FIRST_NAME: Mid(PATRN_NAME,InStr(PATRN_NAME,',')+2)

For MI: MI: Mid(Mid(PATRN_NAME,InStr(PATRN_NAME,',')+2),InStr(Mid(PATRN_NAME,InStr(PATRN_NAME,',')+2),' '),3)