Parse names with a "twist" 2

[dpav29]

I'm working on another list of names that I need to parse into 3 sections (first, Last, Degree. . .they are doctors).

I'm having trouble with first name in some instances when there is a suffix (i.e. Jr. or III).

The format is always Last Name, First Name, Degree

I'm using the following to parse the first name:

Trim(Mid([TempName],InStr(1,[TempName]," ")+1,InStr(InStr(1,[TempName]," "),[TempName],", ")-InStr(1,[TempName],",")-2))

This works fine if the name is: Smith, John, MD, but if it is Smith Jr, John, MD then the first name doesn't parse at all. . .I get a "data conversion type error". I'm sure it has something to do with extra space, but I don't know how to deal with that.

Any help or suggestions is much appreciated as always!

 

[PHV]

In a standard code module create this function:
Public Function getNamePart(strName, intPart As Integer)
Dim tmpArr
If Trim(strName & "") = "" Then Exit Function
tmpArr = Split(strName, ",")
If intPart <= UBound(tmpArr) + 1 Then
getNamePart = Trim(tmpArr(intPart - 1))
ElseIf intPart = 1 Then
getNamePart = strName
End If
End Function
Then in the query grid:
LastName: getNamePart([TempName],1)
FirstName: getNamePart([TempName],2)
Degree: getNamePart([TempName],3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

Looking for " " is causing the problem. Just parse it out on the commas

FirstName = Trim$(Mid$( TempName, Instr(TempName, ",") + 1, InstrRev ( TempName, "," )  - Instr ( TempName, "," )-1))

LastName = Trim$(Left$( TempName, Instr(TempName, ",") - 1 ))

Degree   = Trim$(Mid$(TempName, InstrRev ( TempName, "," ) + 1))