Pulling First Name from a Name field with varying formats

[cliff.clayman]

I am trying to extract first name from a name field that has several different formats. I am using the expression below. The one instance it does not work is when there is no comma. How can I alter this expression to work for both cases?



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

[ranman256]

iif(instr([name],",")=0,Left([Name],InStr([Name]," ")-1) ,Left([Name],InStr([Name],",")-1) )

[cliff.clayman]

That didn't seem to work. Is there a way I can just return the value in the [Name] field if no comma is present?

[June7]

Hasn't this already been addressed in your other thread https://www.accessforums.net/queries...ext-56619.html

Last name or first name - the issues and techniques are the same.

[cliff.clayman]

No, I am still having issues with the function when there are 3 commas in the Name field. I was trying to do this one without the function.

[June7]

As I said - same issues, same responses.

[cliff.clayman]

Is there any way to modify that function to account for when the Name field contains 3 commas? Now I am running into an issue when trying to apply it to this situation as well. The functions returns nothing for a value when there are 3 commas and it fails my validation.

[June7]

Post the function code.

[cliff.clayman]

Function SplitNamesAll(strName As String) As String


          Dim arrSplit() As String
          Dim resF As String
          Dim resM As String
          Dim resL As String
          Dim x As Integer
          Dim sfx As String
          Dim tmp As String
10       On Error GoTo SplitNamesAll_Error


20        sfx = "JrSrEsqIIIDrMrsMs"    'List of suffixes, add more as needed
          '
30    If Trim(strName & "") = "" Then
40      SplitNamesAll = vbNullString
50      Exit Function
60    End If


70    arrSplit = Split(strName, ",", -1)


80    x = UBound(arrSplit)
90        Select Case x
          Case 0
100     resL = Trim(arrSplit(0))   'probably not a person; most likely a company/institutuion
       ' Debug.Print resL & "   *****Not a person; likely a company,estate or institution"
110     SplitNamesAll = resL
120   Case 1
130     resF = Trim(arrSplit(1))
140     resL = Trim(arrSplit(0))
       ' Debug.Print resF + " " + resL
150     SplitNamesAll = resF + " " + resL
160   Case 2
170     resL = Nz(Trim(arrSplit(0)), "")
180     resM = Nz(Trim(arrSplit(1)), "")
190     resF = Nz(Trim(arrSplit(2)), "")


finalcheck:  'is there a suffix?? if so, then adjust as best
             'this code not tested since the patterns are handled above
             '
             'ONLY the Else path is being used in the test data ***************
             '
200     If InStr(sfx, resL) > 0 Then
210         tmp = resM
220         resM = resL
230         resL = resF
240         resF = tmp
           ' Debug.Print "reformatted  " & resF + " " + resL + " " + resM
250         SplitNamesAll = resF + " " + resL + " " + resM
260     Else
           ' Debug.Print resF + " " + resL + " " + resM
270         SplitNamesAll = resF + " " + resL + ", " + resM


280     End If
290   End Select


300      On Error GoTo 0
310      Exit Function


SplitNamesAll_Error:


320       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SplitNamesAll of Module AWF_Related"
End Function

[June7]

Provide an example name with 3 commas.

Might just need to add a Case 3 to the SELECT CASE structure.

Note that I edited you post to put code between CODE tags so indentation is retained and it is easier to read.

[cliff.clayman]

Adam B West, DDS, MD, PA
Azmei A Toews, DDS, MD, PC
Jim K Reed, DDS, Nadeem S Emeril, DDS

Thank you for the edit. I will be sure to do that in the future.

[June7]

Well, your variations are becoming too numerous and complex. A string with two complete names? Really!! How should that be dealt with?

Code to handle every possible combination is virtually impossible. What do you want to do when you encounter that one exception with 4 commas or 5? Or a string with 3 or more complete names?

[cliff.clayman]

I know. I have tried to explain this, but I am just a cog in a giant wheel. I'm just trying to code for as many exceptions as possible. I'm not looking for perfection.

[June7]

What if there is one case of: Carson, Jr, PHD, James S.

Do you want to modify the code again to deal with that? How could code possibly know that Jr is not the first name? Unless you want to build a table of all possible suffixes to check against.

Any possibility of prefixes showing up - Mr., Ms., Dr., Fr., Honorable, Dutchess, Prince, Her Royal Highness, etc.?

Seems the most common arrangements are dealt with. I suggest your code only be altered to return the string unchanged if it doesn't fit the arrangements already dealt with.

[cliff.clayman]

I would be fine with altering the code to return the string unchanged if it doesn't meet the existing arrangements already dealt with.