Name Parsing

[Guest]

I am using Access 97 and I have a query that splits a name field into First, Middle, and Last Name. The name field is in the following format:

Smith, John J.

The formulas I am using are as follows:

Last Name: Left([NAME],InStr(1,[NAME],",")-1)

First Name: Right(Trim([NAME]),Len(Trim([NAME]))-InStr(1,[NAME]," "))

MI: Right(Trim([NAME]),Len(Trim([NAME]))-InStr(InStr(1,[NAME]," ")+1,[NAME]," "))


The problems I am having are that the middle initial always shows in the First Name field and if the name has no middle initial then the whole name appears in the Middle Initial field. Here is an example:

Name Last Name First Name Middle Initial
------ ------------- ------------- -----------------
Smith, John J. Smith John J. J.

Doe, Jane Doe Jane Doe, Jane

Any help would be appreciated. Thank You.

Dave

 

[Allen Browne]

Paste the function below into a standard module, and save.

You can then use:
LastName: LastWord([NAME])


Function LastWord(vPhrase As Variant) As Variant
Dim sPhrase As String ' String of vPhrase
Dim iSpacePos As Integer ' Location of space in sPhrase
Dim iPriorSpace As Integer ' Location of previous space

sPhrase = Trim$(Nz(vPhrase, ""))

Do
iSpacePos = InStr(iSpacePos + 1, sPhrase, " ")
If iSpacePos = 0 Then Exit Do
iPriorSpace = iSpacePos
Loop

If iPriorSpace = 0 Then
LastWord = vPhrase ' 1 word or no words.
Else
LastWord = Mid(sPhrase, iPriorSpace + 1)
End If
End Function


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am using Access 97 and I have a query that splits a name field into

First, Middle, and Last Name. The name field is in the following format:

Smith, John J.

The formulas I am using are as follows:

Last Name: Left([NAME],InStr(1,[NAME],",")-1)

First Name: Right(Trim([NAME]),Len(Trim([NAME]))-InStr(1,[NAME]," "))

MI: Right(Trim([NAME]),Len(Trim([NAME]))-InStr(InStr(1,[NAME]," ")+1,[NAME]," "))


The problems I am having are that the middle initial always shows in the

First Name field and if the name has no middle initial then the whole name
appears in the Middle Initial field. Here is an example:

 

[Dave]

I have a similar problem with Addresses. This is my work around:

SELECT CityStateZip,
Trim(Left([CityStateZip],InStr(1,[CityStateZip],",")-1)) AS City,
Trim(Mid([CityStateZip],Len([City])+2,InStr(Len([City]),[CityStateZip]," ")-Len([City])+1)) AS State,
Trim(Mid([CityStateZip],Len([City])+Len([State])+3,Len([CityStateZip])-Len([City])+Len([State])-3)) AS Zip
FROM Addresses;

The same thing should work for your needs.