First Middle Last name separation?

[pbarila]

Hi,
I have a table with a field that contains first,full middle and last name with 1 space separating each. I want to update the table and put the First,middle initial only and last name in their own fields(Fnamne,MI,Lname) in the same table.
can anyone help?

Thanks

 

[jwindon]

YUCK! Who did that to you!!!!

Anyway, I think maybe someone here with programming skills can help you write some code that would loop through a function to identify each character until it reaches a "" (blank) and then stop. I believe you could do this through the Left(), Mid() or Right() functions. But since the # of characters will be different, you will need some code to check for the spaces.

 

[MikeAngelastro]

Add the three new fields to the table. Then use the code below.

Here is the DAO code:

dim db as Database
dim rs as Recordset

set db = CurrentDB
set rs = db.OpenRecordset("Table Name")

With rs
.MoveFirst
Do until .EOF
!fname = FirstName(FullName field)
!MI = MI(FullName field)
!lname = LastName(FullName field)
.MoveNext
Loop
end with
set rs = Nothing
set db = Nothing

Use the Len, InStr, Right, Left, etc string functions in your user defined fuctions in the code above to get the part of the full name you are seeking.

 

[raskew]

You can also accomplish this with a query. Copy paste the following into a new query in Northwind, then run the query. If this is what you're after, it could easily be turned into an update query to populate LName, FName and MName fields.

SELECT [firstName] & " " & "Middle" & " " & [LastName] AS FullName, Left([FullName],InStr([Fullname]," ")-1) AS MyFirst, Mid([FullName],InStr([FullName]," ")+1) AS IsLeft, Left([Isleft],InStr([IsLeft]," ")) AS MyMiddle, Mid([IsLeft],InStr([isleft]," ")+1) AS MyLast
FROM Employees;

 

[Rich_Lovina]

And if you can follow VB I have a splitter that even removes the occasional "." such as "Mr." or Mrs.", all courtesy our good friend here Doug Leffler. Of course it takes "J" from John into its own initials field, and then puts John in its own field, and if John follows by Alan, as in Mr. John Alan Smith. we yield Fld1= Mr; Fld2 = JA, Fld3 = John and Fld4 = Smith.