Splitting/Parsing Names

[BrokenBiker]

...edit...

Wow...I started typing out the problem/question and ended up typing a novel!

I can be a bit wordy sometimes....

So, here it goes. I'm working on an Access file to use w/ an on-line training record system. The on-line system exports XML files, which I then save & re-name as Excel files. The system exports are based on queries which have info already concocted. Unfortunately, I need to split the name fields.

The examples I've found that I can use I've been able to separate most of the info in the field, but not quite all. ( Example 1 and Example 2) To completely split the info, I have to take the original split/query, run another query to further split the info. Not cool, and too many steps.

To clarify, all the name entries are in the same format:
Amn Smith, John D
SSgt Worker, Joe C
etc.

In the first query w/ VBA, I was able to separate all parts to individual fields except the first two (rank and surname). So, I use the same examples and a second query to separate the info further. That query's result is four fields--Rank, LastName, FirstName, MI

I'm sure there has to be a better way...all in one step.

I admittedly don't know a lot about parsing, whether w/ VBA or in queries. I really think I'm just about to grasp it all, I just need a little lift over the hurdles. Any help is appreciated.

 

[ezfriend]

Per gemma-the-husky's suggestion in your exampl1 above, the split function should to fine.
I copied and pasted the names below into cell A1 on an excel sheet. Run the VBA below using the split fucntion.

Amn Smith, John D
SSgt Worker, Joe C

Sub PerformSplit()

    Dim i As Long
    Dim sName() As String
    
    With Sheet1
        
        'assume there are two names on the sheet starting at row A1 to A2
        For i = 1 To 2
            
            'clean up the array
            Erase sName
            
            'split the name field by a comma
            sName = Split(.Range("A" & i).Value, ",")
                    
            .Range("B" & i).Value = sName(0) 'lastname
            .Range("C" & i).Value = sName(1) 'firstname

            'you can further split the name "SSgt Worker" by declaring another array 
            'and split by a space to get SSgt & Worker into their own column
            
        Next
    
    End With
    
    MsgBox "DONE"
    
End Sub

 

[Brianwarnock]

Whether using the Spilt as per Gemma or Raskew's code first clean up your field.

fld=trim(fld) remove all blanks around text
fld=replace(fld,", ",",") to replace the ,space with just ,
fld=replace(fld," ",",") replace all other intenal spaces with ,

now it is simple to parse

Brian

 

[rahulgty]

Public Function SplitBySpace(FullName As String, intelement As Integer) As String
On Error Resume Next
Dim strResult() As String
strResult = Split([FullName], Chr(32))
SplitBySpace = strResult(intelement)
End Function

Public Function SplitbyComa(FullName As String, intelement As Integer) As String
On Error Resume Next
Dim strResult() As String
strResult = Split([FullName], ",")
SplitbyComa = strResult(intelement)
End Function

Public Function SplitbySlash(FullName As String, intelement As Integer) As String
On Error Resume Next
Dim strResult() As String
strResult = Split([FullName], "/")
SplitbySlash = strResult(intelement)
End Function

 

[BrokenBiker]

I originally posted from home, and didn't have a copy of the db. The original post has a couple of mistakes in it.

Here's the workflow as it stands now:
- Export on-line reports as XML (up to approx. 20 reports)
-- Open, save, and rename as *.xls files: Format "Name dd Mmm yy.xls"
- Use btn/cmd to import data from all Excel files, and then move them to an archive folder.
-- Code is posted here because there have been many run-ins w/ 2007 not being able to FileSearch to import multiple Excel files.

Private Sub Command0_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Documents\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tbl_MyTableName"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
'Kill strPathFile

strFile = Dir()
Loop

'Move imported Excel files to archive folder
Call MoveMsg("C:\Documents\", "C:\Documents\Archive")

End Sub

-- "MoveMsg" is from a module located here.

Now...the imported info already has concocted names, which I have to separate. Currently, the way I'm doing this is to use a module w/ Split-type functions built in, and then call them in two queries. The Example1 and Example2 in the original post are incorrect.

Here's the code for the split functions (both are found from this forum, I just don't have the links):

Public Function ExtractFirstName(AnyName As String) As String
Dim nSpace As Integer
AnyName = Trim(AnyName)
nSpace = InStr(AnyName, " ")
'One word in string
If nSpace = 0 Then
   ExtractFirstName = AnyName
   Exit Function
End If
ExtractFirstName = Left(AnyName, nSpace - 1)
End Function

Public Function ExtractLastName(AnyName As String) As String
Dim nSpace As Integer
AnyName = Trim(AnyName)
nSpace = InStrRev(AnyName, " ")
'One word in string
If nSpace = 0 Then
   ExtractLastName = AnyName
   Exit Function
End If
ExtractLastName = Mid(AnyName, nSpace + 1)
End Function

Public Function SplitFile(intField As Integer, strValue As String, strDelimiter As String) As String
Dim varSplit As Variant
varSplit = Split(strValue, strDelimiter, , vbTextCompare)
SplitFile = varSplit(intField)
End Function

- I then run two queries
-- Qry1 splits the names like this
Rank: ExtractFirstName([EMP_NAME])
FirstName: SplitFile(2,[EMP_NAME]," ")
MI: ExtractLastName([EMP_NAME])
x: SplitFile(0,[EMP_NAME],",")
--- Qry1 returns the values:
Amn Smith, John D
Amn
John
D
Amn Smith
-- Qry2 splits the "Amn Smith" by re-using the ExtractLastName function
LastName: ExtractLastName([x])
--- Qry2 returns the values:
Smith


So...it works, but it's not pretty. I'm sure there's a smoother way of doing this. I haven't dedicated any time to this, or the responses. I will get to the later today or tomorrow.

Thanks for the responses and help. I just wanted to clarify how thing's are working in my db.

 

[BrokenBiker]

WHOO!!! I figured it out! I knew there was a way to use the function that resulted in the rank and surname to further divide down to just the surname, but I couldn't get it.

After much playing, I was able to "adjust" the code as follows:

Public Function ExtractSurname(AnyName As String) As String

'WORKS GOOD--EXTRACTS LAST NAME

Dim RankLastName As String
Dim nSpace As Integer
Dim nSpace2 As Integer
Dim AnyName2 As String

AnyName = Trim(AnyName)
nSpace = InStr(AnyName, ", ")

RankLastName = Left(AnyName, nSpace - 1)

AnyName2 = Trim(RankLastName)
nSpace2 = InStr(AnyName2, " ")

ExtractSurname = Mid(AnyName2, nSpace2 + 1)
    
End Function

I then call it in a query:
Surname: ExtractSurname([EMP_NAME])


It may not seem like much to the more experienced programmers out there, but I'm feeling pretty good about it!

Thanks again for the help. Now off to the next step...

 

[Brianwarnock]

Function parse(fld As String, reqfld As Integer) As String

fld = Trim(fld) 'remove all blanks around text
fld = Replace(fld, ", ", ",") 'to replace the ,space with just ,
fld = Replace(fld, " ", ",") 'replace all other intenal spaces with

myarray = Split(fld, ",")  'build  array
maxvalue = UBound(myarray, 1) 'find max number of fields
If maxvalue < reqfld - 1 Then
parse = " "
Else
parse = myarray(reqfld - 1) 'Select required field from zero based array
End If

End Function

The above function used in a query will return any or all of your fields
firstfieldarse(fldname,1)


Brian

 

[BrokenBiker]

Thanks, Brian! That works great!

For some reason, the first couple of times I ran the query I received a 'Data mismatch' error and all fields were #Name?

...But then it quit doing that, and it all works fine now. Very confusing.

 

[Brianwarnock]

That is confusing, wish I could give an explanation.

Brian

 

[BrokenBiker]

Just a quick update. I haven't received any errors since my last post. Everything's working fine.

Thanks for the help!