How do I the pull the first name from a fullname field in Access?

[Robert in need of a Guru]

I have a field that includes the full name of a customer. Unfortunately, I
never set up separate firstname and lastname fields. Is there some way I can
pull out the firstname, or separate the full name into two firstname and
lastname fields?

 

[Glenn Siswick]

Paste the following two functions into a module and call from queries:

Function CutFirstWord(S, Remainder)
'
' CutWord: returns the first word in S.
' Remainder: returns the rest
'
' Words are delimited by spaces
'
Dim Temp, i As Integer, P As Integer
Temp = Trim(S)
P = InStr(Temp, " ")
If P = 0 Then
CutFirstWord = Temp
Remainder = Null
Else
CutFirstWord = left(Temp, P - 1)
Remainder = Trim(Mid(Temp, P + 1))
End If
End Function

Function CutLastWord(S, Remainder)
'
' CutWord: returns the last word in S.
' Remainder: returns the rest
'
' Words are delimited by spaces.
'
Dim Temp, i As Integer, P As Integer
Temp = Trim(S)
P = 1
For i = Len(Temp) To 1 Step -1
If Mid(Temp, i, 1) = " " Then
P = i + 1
Exit For
End If
Next i
If P = 1 Then
CutLastWord = Temp
Remainder = Null
Else
CutLastWord = Mid(Temp, P)
Remainder = Trim(left(Temp, P - 1))
End If
End Function

Rgds,
Glenn

 

[Glenn Siswick]

For a simpler solution see:

http://www.mvps.org/access/strings/str0001.htm

Regards,
Glenn

 

[John Spencer]

It depends on how the field is formatted on whether or not you will enjoy much
success.

Mary Anne Jones
Jones, Mary Anne
Spencer, Jr, John
Spencer, John Jr
John P Spencer, Jr

IF your Full Name field is ALWAYS in the pattern
Last Name Comma First Name
then the solution is fairly simple.

If the pattern is
First Name Space Last Name
then things get a bit more complex when you have to handle first names that
contain a space (Mary Anne Jones). And I do have a friend whose first name is
"Mary Anne" and includes the space.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Robert in need of a Guru]

Thanks so much Glenn, you are indeed a Guru! This worked like a charm. Even
though my "Name" field is structured as fname lname, it worked. I used the
firstname formula from the website you gave me, replacing the comma separator
with a space separator. I kept getting an error msg for those records which
only included a firstname, so I concatenated a space with the Name field to
trick it into looking for the space separator.

Fname: Left$(([Name] & " "),InStr(1,([Name] & " ")," ")-0)


Voila!!! Thanks man!