[AccessD] Parsing data

Here's a function I use all the time in A97. It's a general solution to the
problem that will let you spit any string using any delimiter.

Lambert

Function Field(ByVal strSource As String, strSep As String, intN As Integer)
As Variant
'Purpose:
'   Returns the Nth element in a delimited list.
'   Input: strSource - the list to search
'             strSep - the delimiter of the list - can be more than one
character
'             intN - the ordinal value of the element to be returned
'
' Null is returned if either string parameter is null, or intN <=0 or
' If the separator string is not found
'
'Str = Field("Chuck*Roberts","*",1) would return "Chuck".
Dim strResult As String
Dim strSearch As String
Dim I As Long
Dim lSep As Long
Dim lRightChars As Long

    lSep = 0: I = 0
    If IsNull(strSource) Or strSource = "" Or IsNull(strSep) Or strSep = ""
Or intN <= 0 Then
        strResult = ""
    Else
        strSearch = strSource
        While I < intN
            lSep = InStr(strSearch, strSep)
            If lSep > 0 Then ' we found the delimiter string
                I = I + 1 ' count occurance
                If I = intN Then ' this is the one we want
                    strResult = Left$(strSearch, lSep - 1)
                End If
                ' strip off i'th field
                strSearch = Right(strSearch, Len(strSearch) - (lSep +
Len(strSep) - 1))
            Else ' did not find our separator string, so return the remainer
of the string if the count is ok
                If I = intN - 1 Then '  we have seen N-1 separator strings,
so this is the field we want
                                                ' at the end of the search
string
                    I = I + 1 ' to terminate the While loop
                    strResult = strSearch
                Else    ' there were less than N-1 fields in the input to
return Null
                    strResult = ""
                    I = intN
                End If
            End If
        Wend
    End If
    If strResult = "" Then
        Field = Null
    Else
        Field = strResult
    End If
End Function

> -----Original Message-----
> From:	Mark A Matte [SMTP:markamatte at hotmail.com]
> Sent:	Wednesday, July 09, 2003 10:58 AM
> To:	accessd at databaseadvisors.com
> Subject:	[AccessD] Parsing data
> 
> Hello All,
> 
> In A97 I need to seperate some data into 2 fields...ex:
> "test--data1"
> test2--data"
> 
> How do I pull left and right of '--'?
> 
> Thanks,
> 
> Mark A. Matte
> 
> _________________________________________________________________
> MSN 8 with e-mail virus protection service: 2 months FREE*  
> http://join.msn.com/?page=features/virus
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com