Extracting first name

[Novice1]

I have a FullName field in which the name is always LastName followed by a comma followed by the first name followed by MI (or middle name) then suffix. We cannot use hypens, spaces or apostophes in last or first names so the extraction should be simple.

Name examples include the following:

Doe, John A
Doe, John
Doe, John Albert
Doe, John Albert Jr.

In a query I want the last name in a separate field; and the first name in another field. The following works with for the LastName

LastName: Mid([FullName],1,InStr([FullName],',')-1)

The following works for first name except when there is no middle name, then I get a #Func! error

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

How can I fix?

 

[jdraw]

The function originally posted had errors.
It has been updated and tested and is listed in post 4 below.

 

[Novice1]

Can I call a function in a query?

 

[jdraw]

I just did a retest of the function and see some issues. It isn't dealing with Firstname and Initial properly???

I will look at it and get back.

Yes, you can use a function in a query.

Update:
Here is a revised/tested function.

'---------------------------------------------------------------------------------------
' Procedure : ParseName
' Author    : ****
' Created   : 2/23/2010
' Purpose   : To parse a field containing the person's full name and to return
' the first name, or the initial if it exists, or last name depending on the
' value of strWhich.
'
' NOTE: The format of the fullname field is
'       Lastname, Firstname Initial(may not be present)
' eg    a)De Jesus, Charlene K.
'       b)O'Sullivan, Margaret
'---------------------------------------------------------------------------------------
' Last Modified: 18-Aug-2016 ***
'
' adjusted logic and tested
' the fullname must be followed by a comma
' a space after the lastname/before first name is optional
'
'     ******
' Inputs: strname == the person's fullname
'         strWhich = F  First Name
'                  = M  Middle Initial
'                  = L  Last Name
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function ParseName(strName As String, strWhich As String) As String


          Dim strUtil As String
          Dim strLastname As String
          Dim strFirstname As String
          Dim strMiddle As String
          Dim PosnOfComma As Integer, PosnOfSpace As Integer

10    On Error GoTo ParseName_Error



20    strUtil = Trim(strName)                'remove any leading/trailing spaces
30    PosnOfComma = InStr(1, strUtil, ",")
40    PosnOfSpace = InStr(PosnOfComma + 1, strUtil, " ")   'looking for a space
          'Debug.Print PosnOfSpace - PosnOfComma
50    strLastname = Left(strUtil, InStr(1, strUtil, ",") - 1)

          'need a check to see if an initial exists


60    strUtil = Mid(strUtil, PosnOfComma + 1)
          'strUtil is now devoid of lastname
70    strUtil = Trim(strUtil)
80    If InStr(strUtil, " ") = 0 Then       'there is no initial
90      strMiddle = vbNullString
100     strFirstname = Trim(strUtil)
110   Else
        'remove leading/trailing spaces
120     strMiddle = Right(strUtil, Len(strUtil) - InStrRev(strUtil, " "))
130     strFirstname = Mid(strUtil, 1, InStr(strUtil, " ") - 1)
140   End If

150   Select Case strWhich
      Case "F"
160     ParseName = strFirstname
170   Case "L"
180     ParseName = strLastname
190   Case "M"
200     ParseName = strMiddle
210   Case Else
220     ParseName = vbNullString
230   End Select
240   On Error GoTo 0
250   Exit Function

ParseName_Error:

260   MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure ParseName of Module Module4"

End Function

And here is a test procedure:

Sub debugIt()
          Dim i As Integer
          Dim namel(2) As String
10        namel(0) = "Smits Grenier,Jaune-Bleu P."
20        namel(1) = "De La Hunter, John"
30        namel(2) = "NoInitial, Robert"

40        For i = 0 To 2
50            Debug.Print ParseName(namel(i), "F") & " " & ParseName(namel(i), "M") & " " & ParseName(namel(i), "L")
60        Next i
End Sub

and here is the test result:

Jaune-Bleu P. Smits Grenier
John De La Hunter
Robert NoInitial


And here is how a query might reference the function:

SELECT TestNames.FullName, ParseName([Fullname],"F") AS Fst
, ParseName([Fullname],"M") AS Mdl 
,ParseName([Fullname],"L") AS Lst
FROM TestNames;

 

[The_Doc_Man]

Novice1, while you certainly CAN manipulate strings this way, the original problem stems from a design flaw. Since your name indicates you might be less experienced, let me share an idea with you.

In your database design, you should have asked the question: What questions will I ask in my database? There is an Old Programmer's Rule that says Access won't tell you anything you didn't tell it first. If you want to know only someone's first name, you should store that information in a separate field.

In other words, if you are going to have to pick apart the name sometimes, STORE it already picked apart. Then you can use a simple QUERY to rejoin (concatenate) the parts in any desired order. Remember that forms and reports want RECORDSET input, but don't care whether the set originated from a table or through a query.

The other "rule" to consider is that a field should be atomic (from Gr. atomos, "indivisible"). You are treating your Fullname field non-atomically. Just remember that in Access, because fields are designed to be treated atomically, it is FAR easier to put them together than to take them apart.

Maybe this doesn't help you immediately - but down the road when you start to implement your next design, take atomicity into consideration.