Split a field in a form

[simaq7]

How would I split a field first a name into two seperate fields?

Example Dow,John
Split into two fields

Last Nmae = Dow
First Name = John

 

[dhookom]

Assuming you have told us the truth, the whole truth, and nothing but the truth:

=Left([Example],Instr([Example],",")-1)
=Mid([Example],Instr([Example],",")+1)

Duane
Hook'D on Access
MS Access MVP

 

[alvechurchdata]

You can also use the Split function which will give you an array:

aNames = Split(myName, ",")
firstName = aNames(0)
lastName = aNames(1)

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[boblarson]

You can also use the Split function which will give you an array

Geoff:

While that is true, it only works in VBA as the SPLIT function is not available directly in queries. The user would need to set up a public function to use the SPLIT function and return the applicable array item.

So, Duane's method is simpler. But I have used your suggestion in the past myself.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[dhookom]

I also have used the Split() function in a couple user-defined functions that can be used in queries and control sources (as well as code).

Public Function ParseAttrib(strText As String, _
        strAttrib As String, _
        strDelimiter As String)
[green]    '---------------------------------------------------------
    ' Procedure : ParseAttrib
    ' Author    : Duane Hookom
    ' Date      : 10/1/2010
    ' Purpose   : Pull a single value from a multi-value string
    '             the strText must use = to match an attribute with its value
    '
    ' Samples
    ' --------------------------------------------------------
    ' ParseAttrib("color=red;Age=50;Dog=False","Color",";")=red
    ' ParseAttrib("color=red;Age=50;Dog=False","dog",";")=False
    '---------------------------------------------------------            [/green]
    Dim arText() As String
    Dim intI As Integer
    On Error GoTo ParseAttrib_Error

    arText() = Split(strText, strDelimiter)
    For intI = 0 To UBound(arText)
        If Split(arText(intI), "=")(0) = strAttrib Then
            ParseAttrib = Split(arText(intI), "=")(1)
        End If
    Next

    On Error GoTo 0
    Exit Function

ParseAttrib_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ParseAttrib of Module basParseText"

End Function

Public Function ParseText(pstrText As String, intElement As Integer, _
        pstrDelimiter As String) As String
    Dim arText() As String
   On Error GoTo ParseText_Error

    arText() = Split(pstrText, pstrDelimiter)
    ParseText = arText(intElement - 1)
    
ExitParseText:
   On Error GoTo 0
   Exit Function

ParseText_Error:
    Select Case Err
        Case 9 [green]'subscript out of range
            'don't do anything[/green]
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ParseText of Module basParseText"
    End Select
    Resume ExitParseText
End Function

Duane
Hook'D on Access
MS Access MVP

 

[ProgramError]

What if they have middle name or hyphonated surname? what about names such as O'toole?
have these been taken into account?


Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[dhookom]

A middle name would possibly create issues. Hyphonated surnames and names like O'toole shouldn't create problems.

Duane
Hook'D on Access
MS Access MVP