Parsing Names - John Doe, Mr. John Doe, Mr. John E. Doe

[cgmarshall]

I have a function to parse names by space. However it John Doe has John in the Prefix field, Doe in the FN field.

Is there some code that will the parts go into the right fields.

Below is my function code.

Function CountSpaceWords(ByVal s) As Integer
' Counts the words in a string that are separated by commas.

Dim WC As Integer, Pos As Integer
If VarType(s) <> 8 Or Len(s) = 0 Then
CountSpaceWords = 0
Exit Function
End If
WC = 1
Pos = InStr(s, " ")
Do While Pos > 0
WC = WC + 1
Pos = InStr(Pos + 1, s, " ")
Loop
CountSpaceWords = WC
End Function
Function GetSpaceWord(ByVal s, Indx As Integer)
' Returns the nth word in a specific field.

Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer
WC = CountSpaceWords(s)
If Indx < 1 Or Indx > WC Then
GetSpaceWord = Null
Exit Function
End If
Count = 1
SPos = 1
For Count = 2 To Indx
SPos = InStr(SPos, s, " ") + 1
Next Count
EPos = InStr(SPos, s, " ") - 1
If EPos <= 0 Then EPos = Len(s)
GetSpaceWord = Trim(Mid(s, SPos, EPos - SPos + 1))
End Function

 

[CharlieT302]

Here's a quick suggestion: Export the table into Excel. Use Excel's "Column to Text" feature (from the Data menu) to parse it out quickly. Then import it back into Access as a new table. Its a lot less time consuming than trying to figure out code.

Be advised though, parsing by space is never clean regardless of the system you are using. Since spaces occur at random places, you may end up with more fields than you want. Its not the end of the world. For example, in Excel you can simply concatenate 'extra' fields back into one by using a formula: A1&" "&B1...much like in Access.

Hope this helps.