Name Parsing

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
Dave said:
I am using Access 97 and I have a query that splits a name field into
Click to expand...
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
Click to expand...
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:
 
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.
 
Back
Top