Hi, how could I separate the data f 2

[nmc1975]

Hi, how could I separate the data from this free field name field into three fields for first, middle, last names?

Example of data: DOE, JOHN JAMES

Need: Fname: JOHN, Mname: James, Lname: DOE

 

[TomKane]

Maybe if you first put commas after each element of the name

DOE, JOHN, JAMES

your could create a comma delimited file and then you could pretty much do anything you wanted with it then.

Hope this helps...

 

[scriverb]

Here is the SQL to parse your name field from table names:
SELECT names.name, Mid$([Name],1,Instr(1,[Name],",&quot-1) AS LName,
Mid$([Name],Instr(1,[Name],",&quot+2, IIF((Instr(Instr(1,[Name],",&quot+2, [Name]," &quot-1)=-1, 99, ((Instr(Instr(1,[Name],",&quot+2, [Name]," &quot-1) - Instr(1,[Name],",&quot )) ) AS FName,
Mid$([Name],IIF((Instr(Instr(1,[Name],",&quot+2, [Name]," &quot-1)=-1, 99, Instr(Instr(1,[Name],",&quot+2 ,[Name]," &quot+1 )) AS MName
FROM names;

Change the red names to whatever the name of the table is in your database.
Bob Scriver