Problem Splitting Name Field Using an Array 3

[RichardOneil]

Hi-

I have a name field that I need to split into first name, last name and middle initial. The format of the field is LASTNAME,FIRSTNAME MI. I have an array that first splits the LASTNAME and FIRSTNAME MI:

SName = rs!Name
nmval2 = Mid$(SName, 1, Len(SName))
MyArray2 = Split(nmval2, ",")
LName = MyArray2(0)
FName = MyArray2(1)

I then repeat the process to split the first name from the MI, using a space instead of the comma as the split value.

nmval3 = Mid$(FName, 1, Len(FName))
MyArray3 = Split(nmval3, " ")
FName = MyArray3(0)
MName = MyArray3(1)

The problem I am running into is that not every record has a middle name so I get a "Subscript is Out of Range Error" on these records. I have tried trapping the null by using IF IsEmpty and If IsNull to no avail. Thanks in advance.

Richard

 

[PHV]

IF nmval3 Like "?* ?*" Then
MyArray3 = Split(nmval3, " ")
FName = MyArray3(0)
MName = MyArray3(1)
Else
MName = ""
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya RichardOneil . . .

Perhaps this:

[blue]   nmval2 = Mid(rs!Name, 1, Len(rs!Name))
   
   If InStrRev(nmval2, " ") = 0 Then
       nmval2 = nmval2 & ","
   Else
       nmval2 = Replace(nmval2, " ", ",")
   End If
   
   MyArray2 = Split(nmval2, ",")
   LName = MyArray2(0)
   FName = MyArray2(1)
   MIni = MyArray2(2)[/blue]

See Ya! . . . . . .

 

[Zion7]

Ya, I was thinking the same thing AceMan but,
I was wondering if there truly was a 'consistency'
in the data; either "Doe,John M" or "Doe,John"

But what if "Du Marier,Elaine"
"Van Dorf,Hans"
or even typo "Doe, john"

To check for ' ', I was thinking maybe
If Mid(rs!Name, Len(rs!Name)-1, 1) = " " Then...
but again, only if 1 letter constitutes all middle initials,
as expected...

Ultimately Richard will let us know.
either way, good suggestion as always AceMan.
...and goes without saying for PHV.

 

[TheAceMan1]

Roger That Zion7 . . .

So what happened to [blue]RichardOneil ?[/blue]

See Ya! . . . . . .

 

[RichardOneil]

I ended up using PHV's suggestion and it worked like a charm. I know that I will have the two word last name scenario to deal with so using a two step approach to splitting the name seems to work best. Many thanks to all for your assistance.

Richard

 

[DavisDS]

I am trying to do sort of the opposite...

I table referencing containging many fields including Rank, LastName, FirstName, MI, and Display. When the record is added the Fist,Last and middle are typed, Rank is a number which references a value on another list, Display is programmatically set as "Rank Last, FIrst MI" ....
Example (Me): SPC Davis, Daniel S.

This works great for poulating dropdown lists and specify Display as the value in a query to poulate at ListBox.

My issue is that for ONE specific ListBox I need to store the values in an Array for manipulation purposes and then pass the Array to the List Box set as "Value List"

When the SPC Davis, Daniel S. Is passed as the next value in the Array instead of it being:

MyArray(index) = "SPC Davis, Daniel S."

It Translates:

MyArray(index) = "SPC Davis,"
MyArray(index+1) = " Davis S."

How can I FORCE the "'" segment to NOT act as a delimiter but as part of the string??

 

[DavisDS]

Private Sub GetList()
'Empty the ListBox: Platton
While Me.Platoon.ListCount > 0
Me.Platoon.RemoveItem (0)
Wend
'Set Private Records sets
' LeaderType 3 = Squad Leader 2 = Team Leader 1 = Member
Set SquadLDRs = CurrentDb().OpenRecordset("SELECT * FROM Soldiers WHERE Platoon = " & ID & " AND LeaderType = 3 ORDER BY Index")
Set TeamLDRs = CurrentDb().OpenRecordset("SELECT * FROM Soldiers WHERE Platoon = " & ID & " AND LeaderType = 2 ORDER BY Index")
Set Members = CurrentDb().OpenRecordset("SELECT * FROM Soldiers WHERE Platoon = " & ID & " AND LeaderType = 1 ORDER BY Index")
Dim Sqd As String
Dim Tm As String
Dim SqdID As Integer
Dim TmID As Integer
While Not SquadLDRs.EOF
Dim Sq As Variant
' Sqd = "SSG Jones, Johnny"
Sqd = SquadLDRs.Fields("Display")
Set TEST = CurrentDb().OpenRecordset("SELECT ID FROM Soldiers Where Display = '" & Sqd & "'")
'SqdID = ID Reference for "SSG Jones, Johnny"
SqdID = TEST.Fields("ID")
'ADD ITEM TO LIST "SSG JONES, JOHNNY"
'.......... , Is acting as Delimitar ...................
Me.Platoon.AddItem (Sqd)
TeamLDRs.MoveFirst
While Not TeamLDRs.EOF
'Search Through Team Leaders Who have Johnny as Superviser, ETC..
Tm = TeamLDRs.Fields("Display")
Set TEST = CurrentDb().OpenRecordset("SELECT ID,Superviser FROM Soldiers Where Display = '" & Tm & "'")
TmID = TEST.Fields("ID")
If TEST.Fields("Superviser") = SqdID Then
'........................................
Me.Platoon.AddItem (Tm)
End If
Members.MoveFirst
While Not Members.EOF
Set TEST = CurrentDb().OpenRecordset("SELECT Superviser FROM Soldiers Where Display = '" & Members.Fields("Display") & "'")
If TEST.Fields("Superviser") = TmID Then
'............................................
Me.Platoon.AddItem (Members.Fields("Display"))
End If
Members.MoveNext
Wend
TeamLDRs.MoveNext
Wend
SquadLDRs.MoveNext
Wend
If Me.Platoon.ListCount > 0 Then
Me.Platoon.Enabled = True
Me.RemoveSoldier.Enabled = True
Me.MoveUp.Enabled = True
Me.Squad.Enabled = True
Me.Team.Enabled = True
Me.Member.Enabled = True
Me.MoveDown.Enabled = True
Else
Me.Platoon.Enabled = False
Me.RemoveSoldier.Enabled = False
Me.MoveUp.Enabled = False
Me.Squad.Enabled = False
Me.Team.Enabled = False
Me.Member.Enabled = False
Me.MoveDown.Enabled = False
End If
Me.Soldiers.Requery
Me.Platoon.Requery

End Sub

This is the Function Used to populate the ListBox ...... Denotes where erro occurs causing value to split to two lines

 

[Zion7]

I used to do this, with my value lists,
"Canada";"Peru";"Lebanon";"Ghana";"...(add quotes)

So Maybe,
Me.Platoon.AddItem (chr(34) & Tm & chr(34))
?