Spliting a single name field into three fields

[neeko1226]

I'm trying split a name field in "Last,First M" format into three seperate fields. Anyone have suggestions?

 

[jrbarnett]

If the format is always
Last, First <space> Middle

you can use InStrto obtain the index of the comma and Mid$ to get the Lastname (it will be index -1 as you don't want the comma).
Then use Instr again starting at index+1 position to locate the space, and use Mid$ to get everything from the first index +1 to there-1 as the first name
Then get everything from that position onwards as the middle name.

John

 

[TheDoubleB]

I use this code behind a button:

Private Sub Command21_Click()
On Error GoTo Err_Command21_Click
Dim dbs
Dim rMST
Dim FN As String
Dim A() As String
Dim I As Integer
Set dbs = CurrentDb()
Set rMST = dbs.OpenRecordset(&quot;Select * from EnterTableNameHere Where ([FullName] > ' ')&quot
Do Until rMST.EOF
FN = rMST![FullName]
A = Split(FN)

For I = LBound(A) To UBound(A)
If I = 0 Then
rMST.Edit
rMST.Fields(&quot;First_Name&quot = A(I)
rMST.Update
End If

If I = 1 Then
rMST.Edit
rMST.Fields(&quot;Middle_Name&quot = A(I)
rMST.Update
End If

If I = 2 Then
rMST.Edit
rMST.Fields(&quot;Last_Name&quot = A(I)
rMST.Update
End If

If I = 4 Then
rMST.Edit
rMST.Fields(&quot;Suffix&quot = A(I)
rMST.Update
End If

Next
rMST.MoveNext
Loop

MsgBox (&quot;Parsing Complete&quot

Exit_Command21_Click:
Exit Sub

Err_Command21_Click:
MsgBox Err.Description
Resume Exit_Command21_Click
End Sub

Make sure that the field names (First_Name, Middle_Name, Last_Name, Suffix) exist in the table.

TheDoubleB

 

[neeko1226]

That sounds like it would work, but I'm an extreem beginer in VB so I'm not sure how to set it all up. Here's what I have written so far:
Dim stDocName As String

Dim Sess0 As Object
Dim validacct As Variant
Dim LName As Variant
Dim FName As Variant
Dim FullName As String
Dim rs As Recordset
Dim db As Database


Set Sess0 = GetObject(&quot;C:\Program Files\E!Pc\Sessions/Fdr.edp&quot

Sess0.Screen.SendKeys (&quot;<Home>&quot & (&quot;bs &quot & AcctNum
Sess0.Screen.SendKeys (&quot;<Enter>&quot
Sess0.Screen.waithostquiet (500)
Sess0.Screen.SendKeys (&quot;nm na&quot & (&quot;<Enter>&quot
Sess0.Screen.waithostquiet (500)


validacct = Sess0.Screen.Select(3, 13, 3, 28)
If validacct = AcctNum Then
FullName = Sess0.Screen.Select(5, 28, 5, 52)

&quot;FullName&quot; is the field in &quot;Last,First M&quot; format, and I'm trying to populate the first name and last name fields seperately. Thanks.

Nick

 

[zevw]

Dim Client As String
Dim FName As String
Dim LName As String
Dim MidName As String

Client = &quot;Last,First M&quot;

FName = Mid(Client, 1, InStr(1, Client, &quot;,&quot - 1)
LName = Mid(Client, InStr(1, Client, &quot;,&quot + 1, (InStr(1, Client, &quot; &quot - 1) - (InStr(1, Client, &quot;,&quot))
MidName = Mid(Client, InStr(1, Client, &quot; &quot + 1, Len(Client) - (InStr(1, Client, &quot; &quot + 1))

 

[TheDoubleB]

My code is looking for a SPACE between values. You can replace the &quot;,&quot; with &quot; &quot; and it will parse correctly. You can cut and paste the code behind the On_Click event of a button on any form, just change the &quot;Command21&quot; in the code with your button name.

 

[TheDoubleB]

Sorry, there were a few typos in the first piece of code I sent.

Private Sub Command21_Click()
On Error GoTo Err_Command21_Click
Dim dbs
Dim rMST
Dim FN As String
Dim A() As String
Dim I As Integer
Set dbs = CurrentDb()
Set rMST = dbs.OpenRecordset(&quot;Select * from EnterTableNameHere Where ([FullName] > ' ')&quot
Do Until rMST.EOF
FN = rMST![FullName]
A = Split(FN)
For I = LBound(A) To UBound(A)
If I = 0 Then
rMST.Edit
rMST.Fields(&quot;Last_Name&quot = A(I)
rMST.Update
End If

If I = 1 Then
rMST.Edit
rMST.Fields(&quot;First_Name&quot = A(I)
rMST.Update
End If

If I = 2 Then
rMST.Edit
rMST.Fields(&quot;Middle_Name&quot = A(I)
rMST.Update
End If
Next
rMST.MoveNext
Loop

MsgBox (&quot;Parsing Complete&quot

Exit_Command21_Click:
Exit Sub

Err_Command21_Click:
MsgBox Err.Description
Resume Exit_Command21_Click
End Sub

 

[neeko1226]

Ya'll are Awesome! It's working like a charm. Thanks to all of you for your help!

Nick