Split a Field easily

[dcrosier]

How do I easily split a field into two seperate fields?

For example Smith, Jane parsed into two separate fields:
First Name = Jane
Last Name = Smith

And then how would I do it if the data is NOT separated by commas?
What is the syntex. Someone told me I should use the TRIM function???

Thanks
Dawn

 

[RickSpr]

In Access 2000, there is a Split() function that will break a string into substrings separated by a specified delimiter. The substrings are returned in a variant array of strings.

In Access 97, you can use the following function which works almost identically:

Public Function Split(Expression As String, Optional Delimiter, Optional Limit As Long = -1, _
                      Optional Compare As Integer = vbBinaryCompare) As Variant
' Purpose: Split a string into substrings at a delimiter, and return the substrings
'          in a Variant (Array of String)
' Accepts: 1. String expression to be split
'          2. Optional delimiter (default is ' ')
'          3. Optional maximum of number of substrings to return (default is all substrings)
'          4. Optional compare mode for delimiter (default is case-sensitive)
' Returns: Variant array of strings containing substrings separated by the delimiter. If
'          Limit is reached, last entry in array contains remainder of string, including
'          any delimiters embedded.
    Dim result()
    Dim i As Integer, j As Integer, count As Integer
    
    If Limit < -1 Then Err.Raise 5
    If IsMissing(Delimiter) Then Delimiter = &quot; &quot;
    If Delimiter = &quot;&quot; Then
        ReDim result(0)
        result(0) = Expression
        Split = result
        Exit Function
    End If
    If Expression = &quot;&quot; Then
        Split = Array()
        Exit Function
    End If
    i = 1
    Do
        If (Limit >= 0) And (count >= Limit - 1) Then Exit Do
        j = InStr(i, Expression, Delimiter, Compare)
        If j = 0 Then Exit Do
        ReDim Preserve result(count)
        result(count) = Mid$(Expression, i, j - i)
        count = count + 1
        i = j + Len(Delimiter)
    Loop
    ReDim Preserve result(count)
    result(count) = Mid$(Expression, i)
    Split = result
End Function

Rick Sprague

 

[THoey]

This code should work based on the assumption that there will either be a ", " or a " " separating the last name and the first name. I didn't test it, but it is modified from code that I know works.

Public Function ParseName()
    Dim db As Database
    Dim rst As Recordset
    Dim WholeName As String
    Dim pos1 As Integer
    Dim pos2 As Integer
        
    Set db = CurrentDb
    Set rst = db.OpenRecordset("select * from tblEmployees")
    rst.MoveFirst
    Do While Not rst.EOF
        rst.Edit
        WholeName = LTRIM(RTRIM(rst!Name))
        IF InStr(1, WholeName, ",") > 0 THEN
            pos1 = InStr(1, WholeName, ",") - 1
            pos2 = pos1 + 3
        ELSE
            pos1 = InStr(1, WholeName, " ") - 1
            pos2 = pos1 + 2
        END IF
        rst!LastName = Left(WholeName, pos1)
        rst!FirstName = Mid(WholeName, pos2)
        rst.Update
        rst.MoveNext
    Loop
    rst.Close
    db.Close
End Function

For simplicity sake later on, make sure you name your new fields without a space, instead of like in your example.

Hope this helps...
Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.