Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help needed parsing string... (97 SR-2)

    I need to fix a procedure I wrote a year ago... I'm trying to parse a string... I need to find the three character month in the string and set a position for it... (This is used to set the Strike later)... Here's the code I was using...

    Public Function ParsePosition(pstrSearch As String) As Integer

    ' This function returns the position that will be used for parsing
    ' the long description field from the WTR or DTR

    Select Case True
    Case InStr(6, pstrSearch, " JAN")
    ParsePosition = InStr(6, pstrSearch, " JAN")
    Case InStr(6, pstrSearch, " FEB")
    ParsePosition = InStr(6, pstrSearch, " FEB")
    Case InStr(6, pstrSearch, " MAR")
    ParsePosition = InStr(6, pstrSearch, " MAR")
    Case InStr(6, pstrSearch, " APR")
    ParsePosition = InStr(6, pstrSearch, " APR")
    Case InStr(6, pstrSearch, " MAY")
    ParsePosition = InStr(6, pstrSearch, " MAY")
    Case InStr(6, pstrSearch, " JUN")
    ParsePosition = InStr(6, pstrSearch, " JUN")
    Case InStr(6, pstrSearch, " JUL")
    ParsePosition = InStr(6, pstrSearch, " JUL")
    Case InStr(6, pstrSearch, " AUG")
    ParsePosition = InStr(6, pstrSearch, " AUG")
    Case InStr(6, pstrSearch, " SEP")
    ParsePosition = InStr(6, pstrSearch, " SEP")
    Case InStr(6, pstrSearch, " OCT")
    ParsePosition = InStr(6, pstrSearch, " OCT")
    Case InStr(6, pstrSearch, " NOV")
    ParsePosition = InStr(6, pstrSearch, " NOV")
    Case InStr(6, pstrSearch, " DEC")
    ParsePosition = InStr(6, pstrSearch, " DEC")
    End Select

    End Function

    Problem: Some records have descriptions like this...
    PUT BK NOVA SCOT DEC 065
    PUT BK NOVA SCOT NOV 065
    CALL BK NOVA SCOT NOV 070

    There is not always a space after the NOV, before the numeric value either...
    CALL BK NOVA SCOT NOV110 1/2

    InStr finds the first occurance... I want to either be more specific... like ask for ... space, MMM value, # value OR space, MMM, space, # (...but I can't use wildcard characters with InStr()...) OR maybe find the last occurance in the string rather than the first...

    Does anyone have any ideas of how I could accurately do this?? TIA!

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Help needed parsing string... (97 SR-2)

    <P ID="edit" class=small>(Edited by MarkD on 03-Nov-03 14:31. Added comment.)</P>Recommend use InStrRev function to search string backwards as in this example:

    Public Function GetMonthPosRev(ByRef strTxt As String) As Integer

    Dim strMonth(1 To 12) As String ' Array
    Dim n As Integer
    Dim intPos As Integer

    For n = 1 To 12
    ' Populate array:
    strMonth(n) = UCase$(Format$(DateSerial(Year(Date), n, 1), "mmm"))
    Next n

    For n = 1 To 12
    intPos = InStrRev(strTxt, Chr$(32) & strMonth(n), , vbTextCompare)
    If Mid$(strTxt, intPos + 4, 1) = Chr$(32) Then
    If IsNumeric(Mid$(strTxt, intPos + 5, 1)) Then
    Exit For
    End If
    ElseIf IsNumeric(Mid$(strTxt, intPos + 4, 1)) Then
    Exit For
    Else
    ' keep checking
    End If
    Next n

    ' Position of month is 1 after the space:
    GetMonthPosRev = intPos + 1

    End Function

    Example of use:

    ? GetMonthPosRev("CALL BK NOVA SCOT DEC 070")
    19
    ? GetMonthPosRev("CALL BK NOVA SCOT NOV110 1/2")
    19
    ? GetMonthPosRev("CALL BK MARY SCOT DEC110 1/2")
    19

    You may need to modify as necessary, this is based on sample text strings provided, function assumes there will be either space & number, or just number, after the month. Chr$(32) returns a space. Note use of array to simplify things somewhat; Format function will return the 3-letter month abbreviations. Your examples all upper case, so I used UCase, to be safe specified textual comparison for InStrRev (ie, not case-sensitive).

    PS: Noticed you are using Access 97 - sorry, InStrRev function not available in ACC 97. Above example would need to be modified.

    HTH

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed parsing string... (97 SR-2)

    Thanks Mark... but I'm still having problems... Since I can't use InStrRev, I have no clue how I was supposed to adapt this for my purposes...
    I must be brain dead today... Sorry... <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    We really need a smilie for STRESSED OUT!! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help needed parsing string... (97 SR-2)

    Add this function, and replace InStrRev(strTxt, Chr$(32) & strMonth(n), , vbTextCompare) in Mark's code by InstrRev97(strTxt, Chr$(32) & strMonth(n))

    Function InStrRev97(StringCheck, StringMatch, Optional Start As Long = -1)
    Dim lngIndex As Long
    Dim lngLen As Long
    Dim lngStart As Long
    If IsNull(StringCheck) Or IsNull(StringMatch) Then
    InStrRev97 = Null
    ElseIf StringCheck = "" Then
    InStrRev97 = 0
    ElseIf StringMatch = "" Then
    InStrRev97 = Start
    Else
    InStrRev97 = 0
    lngLen = Len(StringMatch)
    If Start = -1 Then
    lngStart = Len(StringCheck) - lngLen + 1
    Else
    lngStart = Start - lngLen + 1
    End If
    For lngIndex = lngStart To 1 Step -1
    If UCase(Mid(StringCheck, lngIndex, lngLen)) = UCase(StringMatch) Then
    InStrRev97 = lngIndex
    Exit For
    End If
    Next lngIndex
    End If
    End Function

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed parsing string... (97 SR-2)

    Thanks Hans... Now it's returning 0 for the position for every record... I don't know what's wrong at the moment but I guess I'll have to keep trying... It was working find the first way I had it..... except now with this exception... <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    I really think I need to go forward and check for "Chr(32) MMM Chr(32) and a number" or "Chr(32) MMM and a number"... but right now, I don't have a clue how...

    This HAS to happen at Year End right?? <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help needed parsing string... (97 SR-2)

    I used this:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Function InStrRev97(StringCheck, StringMatch, Optional Start As Long = -1)
    Dim lngIndex As Long
    Dim lngLen As Long
    Dim lngStart As Long
    If IsNull(StringCheck) Or IsNull(StringMatch) Then
    InStrRev97 = Null
    ElseIf StringCheck = "" Then
    InStrRev97 = 0
    ElseIf StringMatch = "" Then
    InStrRev97 = Start
    Else
    InStrRev97 = 0
    lngLen = Len(StringMatch)
    If Start = -1 Then
    lngStart = Len(StringCheck) - lngLen + 1
    Else
    lngStart = Start - lngLen + 1
    End If
    For lngIndex = lngStart To 1 Step -1
    If UCase(Mid(StringCheck, lngIndex, lngLen)) = UCase(StringMatch) Then
    InStrRev97 = lngIndex
    Exit For
    End If
    Next lngIndex
    End If
    End Function

    Public Function GetMonthPosRev(ByRef strTxt As String) As Integer
    Dim strMonth(1 To 12) As String ' Array
    Dim n As Integer
    Dim intPos As Integer
    For n = 1 To 12
    ' Populate array:
    strMonth(n) = UCase$(Format$(DateSerial(Year(Date), n, 1), "mmm"))
    Next n
    For n = 1 To 12
    intPos = InStrRev97(strTxt, Chr$(32) & strMonth(n))
    If Mid$(strTxt, intPos + 4, 1) = Chr$(32) Then
    If IsNumeric(Mid$(strTxt, intPos + 5, 1)) Then
    Exit For
    End If
    ElseIf IsNumeric(Mid$(strTxt, intPos + 4, 1)) Then
    Exit For
    Else
    ' keep checking
    End If
    Next n
    ' Position of month is 1 after the space:
    GetMonthPosRev = intPos + 1
    End Function

    <img src=/w3timages/blueline.gif width=33% height=2>

    I typed ? GetMonthPosRev("CALL BK NOVA SCOT DEC 070") in the Immediate window and got 19 as result.

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed parsing string... (97 SR-2)

    Okay... It's settled... I'm a complete idiot today!

    I had commented out the last line of Mark's function... GetMonthPosRev = intPos + 1... because I didn't want it to add one to the position...
    So guess what?... The function was returning 0...
    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    I'm kinda concerned about the efficiency of these functions though... Maybe I'm wrong (Very Probably!!!) but I was stepping through the code and mannn does it do a lot of looping for each string... The query is using this on over 50,000 records... Who knows... I suppose something similar is happening behind the scenes when I use InStr() too... Oh well...

    It works!!! ...Thank you both VERY VERY MUCH for the help!!!!!! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/hugs.gif border=0 alt=hugs width=41 height=25> <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15> <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help needed parsing string... (97 SR-2)

    These functions are certainly not very efficient, but that's only to be expected. If the descriptions are more or less static, and the overhead of the calculations is a problem, this could be one of those situations in which it pays to store the result of the calculation in a separate field. Normally, I always advise against storing derived data, but sometimes it can speed things up tremendously.

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Help needed parsing string... (97 SR-2)

    When I got a chance, I tried doing this another way w/o using InStrRev (I haven't used ACC 97 in years so usually don't have this problem). Example:

    Public Function GetMonthPos(ByRef strTxt As String) As Integer

    Dim strMonth(1 To 12) As String ' Array
    Dim n As Integer
    Dim intPos As Integer

    For n = 1 To 12
    ' Populate array:
    strMonth(n) = UCase$(Format$(DateSerial(Year(Date), n, 1), "mmm"))
    intPos = GetPosition(strTxt, strMonth(n))
    If intPos > 0 Then
    Exit For
    End If
    Next n

    GetMonthPos = intPos

    End Function

    First function calls this 2nd function till positive results are found:

    Function GetPosition(ByRef strTxt As String, ByRef strMonth As String) As Integer

    Dim intPos As Integer
    Dim n As Integer

    For n = 1 To Len(strTxt)
    intPos = InStr(n, strTxt, Chr$(32) & strMonth, vbTextCompare)
    If intPos > 0 Then
    If Mid$(strTxt, intPos + 4, 1) = Chr$(32) Then
    If IsNumeric(Mid$(strTxt, intPos + 5, 1)) Then
    Exit For
    End If
    ElseIf IsNumeric(Mid$(strTxt, intPos + 4, 1)) Then
    Exit For
    Else
    ' keep checking:
    n = intPos + 1
    End If
    Else ' not found (intPos = 0)
    Exit For
    End If
    Next n

    ' Position of month is 1 after the space:
    If intPos > 0 Then
    GetPosition = intPos + 1
    Else
    GetPosition = 0
    End If

    End Function

    Test usage:

    ? GetMonthPos("CALL BK NOVA SCOTIA NOV110 1/2")
    21
    ? GetMonthPos("CALL BK HAIL MARY MAR 110 1/2")
    19
    ? GetMonthPos("CALL BK HAIL MARY SCOT XXX110")
    0

    I'm sure this is not most efficient code, but unless you can simplify the problem somehow (standard format for incoming data, or have some idea what month to look for) I have no brilliant suggestions...

    HTH

  10. #10
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed parsing string... (97 SR-2)

    I agree with you Hans... When I started in this department I found that there were TONS of make table queries in about every mdb I looked at.... I almost never use them now... Except during the design process... OR when a query is getting way too slow because of the amount of levels of joins and subqueries...

    Someone much more experienced than I once told me "If you have to make tables during daily processing... the database design isn't right... " ...In most cases, if not all, I agree with him...

    This seems to be processing in a reasonable amount of time at this point, so I'll leave it for now...
    Again... Thanks a million!

  11. #11
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed parsing string... (97 SR-2)

    Thanks Mark!

    I have it working now... We'll be upgrading soon... The boss said "Not until AFTER year end!" <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    I like your idea about "standard format for incoming data"... BUT
    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> Ever heard of ADP?? 'Nuff said... <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Help needed parsing string... (97 SR-2)

    If you want to use pattern matching, you can use this modified version of GetPosition function as shown in previous reply:

    Function GetPosition(ByRef strTxt As String, ByRef strMonth As String) As Integer

    Dim intPos As Integer
    Dim n As Integer

    For n = 1 To Len(strTxt)
    intPos = InStr(n, strTxt, Chr$(32) & strMonth, vbTextCompare)
    If intPos > 0 Then
    If Mid$(strTxt, intPos, 6) Like Chr$(32) & strMonth & Chr$(32) & "#" Or _
    Mid$(strTxt, intPos, 5) Like Chr$(32) & strMonth & "#" Then
    Exit For
    Else
    ' keep checking:
    n = intPos + 1
    End If
    Else ' not found (intPos = 0)
    Exit For
    End If
    Next n

    ' Position of month is 1 after the space:
    If intPos > 0 Then
    GetPosition = intPos + 1
    Else
    GetPosition = 0
    End If

    End Function

    You can test this function against previous example & see which, if any, is more efficient (the "Like" operator is allegedly not very efficient for some type of comparisons). I tested both versions of GetMonthPos function in query, used with five different text & memo fields, in table with 10,000 records, for a total of 50,000 iterations, & there was very little delay (if any) in query displaying records. Both versions seemed to execute equally quickly. Your results may vary. The function returned correct results with sample text strings. Note if using Like operator, the "#" symbol is used to denote any number:

    ? "JAN1" Like "JAN#"
    True

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •