Parse String

[Purdue2479]

I am trying to use the following code to parse out the year from a string, but only want the first and last instances.

Example:

I would want the result to be "2007-2009" from the string below

"Tbl_Payment_YTD.[Rebate Period] Like " & Chr(34) & "*2007" _
& Chr(34) & "Or Tbl_Payment_YTD.[Rebate Period] Like " & Chr(34) & "*2008" & Chr(34) & "Or Tbl_Payment_YTD.[Rebate Period] Like " & Chr(34) & "*2009" & Chr(34)

Any ideas?

Sub GetIt()
Dim i As Integer
Dim strText As String
Dim iPositions() As Integer
Dim iStart As Integer
Dim iLoop As Integer
Dim strCriteria As String

strText = "Tbl_Payment_YTD.[Rebate Period] Like " & Chr(34) & "*2007" _
& Chr(34) & "Or Tbl_Payment_YTD.[Rebate Period] Like " & Chr(34) & "*2008" & Chr(34)

'Find out how many "*" are within the string by subtracting _
 Len("Ihave/four/OfThese/In/Me") from Len("IhavefourOfTheseInMe") _
 This will result in four. We then take 1 because the first element _
 in an Array is always zero, unless told otherwise.
iLoop = Len(strText) - Len _
        (Application.Substitute(strText, "*", "")) - 1

'Tell the array how many elements it is to hold.
ReDim iPositions(iLoop) As Integer
iStart = 1

    For i = 0 To iLoop 'loop four times
        'Parse the position of the nth "*" Starting from iStart .
        iPositions(i) = InStr(iStart, strText, "*")

        'Add one to the found position, for next InStr to Start from.
        iStart = iPositions(i) + 1
        strCriteria = strCriteria & Mid(strText, iPositions(i) + 1, 4) & "-"

    Next i
    
    strCriteria = Left(strCriteria, Len(strCriteria) - 1)
    
    MsgBox strCriteria

End Sub

 

[ErikSnoek]

It's a bit different from the code you posted, but it does the job:

Sub GetIt()
    Dim strText As String
    Dim strPositions() As String
    Dim strFirst As String, strLast As String
    Dim strCriteria As String
    
    strText = "Tbl_Payment_YTD.[Rebate Period] Like " & Chr(34) & "*2007" & Chr(34) & "Or Tbl_Payment_YTD.[Rebate Period] Like " & Chr(34) & "*2008" & Chr(34) & "Or Tbl_Payment_YTD.[Rebate Period] Like " & Chr(34) & "*2009" & Chr(34)
    
    strPositions = Split(strText, "*")
    
    strFirst = left(strPositions(1), 4)
    strLast = left(strPositions(UBound(strPositions)), 4) 'fyi: Ubound gets the size of an array
    strCriteria = strFirst & "-" & strLast
    
    MsgBox strCriteria
End Sub

 

[Purdue2479]

Thx. I ended up using the following:

Function Get_Yr()

Dim i As Integer
Dim strText As String
Dim strYr As String
Dim iPositions() As Integer
Dim iStart As Integer
Dim iLoop As Integer

strText = strCriteria

iLoop = Len(strText) - Len (Replace(strText, "*", "")) - 1

ReDim iPositions(iLoop) As Integer
iStart = 1

    For i = 0 To iLoop 

        iPositions(i) = InStr(iStart, strText, "*")
        iStart = iPositions(i) + 1
        strYr = strYr & Mid(strText, iPositions(i) + 1, 4) & "-"

    Next i
    
    strYr = Left(strYr, Len(strYr) - 1)
    
    Get_Yr = Left(strYr, 4) & "-" & Right(strYr, 4)
    
End Function