Name Parsing

  • Thread starter Thread starter floridagal
  • Start date Start date
F

floridagal

I have a list of names like this:
Smith, Pat
Brown, Clifford G

I can parse out the first name using the below syntax but
it gives me the middle initial when it exists:
First Name: IIf(IsNull([EMPLOYEE_ASSIGNMENTS]!
[SUPERVISOR_NAME]),'',Right(Trim([Supervisor Name]),Len
(Trim([Supervisor Name]))-InStr(1,[Supervisor Name]," ")))

How can I get rid of the Middle Initial when there is one?

TIA!
 
Hi,

I would tackle this something like:

Find out the position of the ','
Take all the characters to the right of that position
Trim to remove any leading spaces
If there is a space in here, take all the characters to the left of the
space.

eg

Smith, Pat

',' in position 6
take characters to right of character 6 - ' Pat'
trim - 'Pat'
if there a space in this string? - NO, so return string as it is.

Brown, Clifford G

',' in position 6
take characters to right of character 6 - ' Clifford G'
trim - 'Clifford G'
if there a space in this string? - YES, so:
where is the space? - pos 9
take characters to left of pos 9 = 'Clifford'

You should be able do all this with the 'IIf', 'InStr' and 'Left', 'Right'
etc. functions that you already use.

Good luck!

ChrisM
 
Dim sName As String
Dim sSurname As String
Dim sMInitial As String
Dim sFInitial As String

sName = "Brown, Clifford G"

sName = Trim(sName)

'Get the surname
If InStr(1, sName, ",") > 0 Then
sSurname = Left(sName, InStr(1, sName, ",") - 1)
End If

'Get the first initial
sName = Trim(Replace(sName, sSurname, ""))
sName = Trim(Replace(sName, ",", ""))
sFInitial = Left(sName, 1)

If InStr(1, sName, " ") > 0 Then
sMInitial = Mid(sName, InStrRev(sName, " ", -1) + 1)
End If

Debug.Print sSurname
Debug.Print sFInitial
Debug.Print sMInitial

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Back
Top