Help needed parsing string… (97 SR-2)

Topic

New Reply

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!

Reply | Quote

Replies

(Edited by MarkD on 03-Nov-03 14:31. Added comment.)

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

Reply | Quote

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…

We really need a smilie for STRESSED OUT!!

Reply | Quote

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

Reply | Quote

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…

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??

Reply | Quote

I used this:

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

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

Reply | Quote

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…

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!!!!!!

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Thanks Mark!

I have it working now… We’ll be upgrading soon… The boss said “Not until AFTER year end!”

I like your idea about “standard format for incoming data“… BUT
Ever heard of ADP?? ‘Nuff said…

Reply | Quote

Thanks Mark!

I have it working now… We’ll be upgrading soon… The boss said “Not until AFTER year end!”

I like your idea about “standard format for incoming data“… BUT
Ever heard of ADP?? ‘Nuff said…

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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…

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!!!!!!

Reply | Quote

I used this:

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

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

Reply | Quote

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…

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??

Reply | Quote

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

Reply | Quote

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…

We really need a smilie for STRESSED OUT!!

Reply | Quote

(Edited by MarkD on 03-Nov-03 14:31. Added comment.)

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

Reply | Quote