[AccessD] Strip out text

Back in my Access 97 days I wrote a "Field()" function which allowed me to
pick off parts of strings using any arbitrary delimiter. I find this handy
when I need to parse a string that uses *different* delimiters, say an
underscore at one point and a comma at another.

Here's the code

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 occurrence
                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
remainder 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

For your application you could do what you want using it and a simple loop

Dim vResult as Variant
Dim n as Long
	n = 1
	vResult = Field(someString,"_",n)
	While Not IsNull(vResult)
		' do something with vResult
		n = n + 1
		vResult = Field(someString,"_",n)
	Wend

HTH

Lambert



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Tuesday, April 04, 2006 1:52 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Strip out text


Thanks for the offer, but I moved it to another machine with A2k and Split 
worked...but If I have to go back to 97...I'll use the instr and mid.

Thanks again for the info.

Mark A. Matte


>From: "Jim DeMarco" <Jdemarco at hudsonhealthplan.org>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem solving" 
><accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Strip out text
>Date: Tue, 4 Apr 2006 13:29:59 -0400
>
>No A97 doesn't support Split, sorry.  You can find a Split replacement 
>on Codehound.com though and run with that.  Another option is to use
>Instr() and Mid() functions to walk through the text.  InStr to find 
>the location of the trailing underscore and Mid to grab whatever piece 
>of text you need (from the beginning of the string or the previous 
>Instr location stored in a variable).
>
>Let me know if you need an example (Split's a lot easier though).  I 
>can probably whip up some pseudo-code if need be.
>
>
>Jim D.
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
>Sent: Tuesday, April 04, 2006 1:18 PM
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Strip out text
>
>Jim,
>
>Thanks for the example...I am using A97...and it does not recognize 
>"Split" Am I missing a reference...or need a later version of Access?
>
>Thanks,
>
>Mark A. Matte
>