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.
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("Select * from EnterTableNameHere Where ([FullName] > ' ')"
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("First_Name" = A(I)
rMST.Update
End If
If I = 1 Then
rMST.Edit
rMST.Fields("Middle_Name" = A(I)
rMST.Update
End If
If I = 2 Then
rMST.Edit
rMST.Fields("Last_Name" = A(I)
rMST.Update
End If
If I = 4 Then
rMST.Edit
rMST.Fields("Suffix" = A(I)
rMST.Update
End If
Next
rMST.MoveNext
Loop
MsgBox ("Parsing Complete"
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.
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("C:\Program Files\E!Pc\Sessions/Fdr.edp"
validacct = Sess0.Screen.Select(3, 13, 3, 28)
If validacct = AcctNum Then
FullName = Sess0.Screen.Select(5, 28, 5, 52)
"FullName" is the field in "Last,First M" format, and I'm trying to populate the first name and last name fields seperately. Thanks.
My code is looking for a SPACE between values. You can replace the "," with " " 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 "Command21" in the code with your button name.
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("Select * from EnterTableNameHere Where ([FullName] > ' ')"
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("Last_Name" = A(I)
rMST.Update
End If
If I = 1 Then
rMST.Edit
rMST.Fields("First_Name" = A(I)
rMST.Update
End If
If I = 2 Then
rMST.Edit
rMST.Fields("Middle_Name" = A(I)
rMST.Update
End If
Next
rMST.MoveNext
Loop
MsgBox ("Parsing Complete"
Exit_Command21_Click:
Exit Sub
Err_Command21_Click:
MsgBox Err.Description
Resume Exit_Command21_Click
End Sub
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.