Help the error handling.

**Haggrr** · 11-21-2014, 03:36 PM

Please excuse my lack of technical jargon here.

The module below works incredibly well. It currently only hangs on one 'style number' that is being un-concatenated, specifically 'STBK-75', where the root string doesn't end in 'w' or a number.<P>I need a error handler to jump pass those instances. And not one that excepts the value of 'k', as there may be a few more in the future.
Also I need to know where in this Module to place it. Thanks

Public Function fGetFirstChars_Nums_w(pString As Variant) As String
Dim tmp As String
Dim tmpStr As String
Dim strRemStr As String
Dim strNxtChar As String
Dim strPrevChar As String
Dim strW As String
Dim bytChrLoc As Byte
Dim bytWLoc As Byte
Dim bytRemLen As Byte
Dim bytStrLen As Byte
Dim strHyphen As String
Dim cntr
Dim NoNumber As Boolean
Dim NoW As Boolean

'set the values of the flags
NoNumber = False
NoW = False
If Len(Trim(pString & "")) > 0 Then
'rule "B" - if the string contains a "/"
bytChrLoc = InStr(1, pString, "/")
If bytChrLoc > 0 Then
'the following code will loop until the
'previous character is numberic
FindLastNum:
'Character before the "/" must be a number
strPrevChar = MID(pString, bytChrLoc - 1, 1)
If IsNumeric(strPrevChar) Then
tmp = Left(pString, bytChrLoc - 1)
'next check for any number following the "/"
strRemStr = Right(pString, Len(pString) - bytChrLoc)
bytRemLen = Len(strRemStr)
For cntr = 1 To bytRemLen
strNxtChar = MID(strRemStr, cntr, 1)
If IsNumeric(strNxtChar) Then

strNxtChar = MID(strRemStr, cntr, 1)
tmp = tmp + "/" + strNxtChar
GoTo ChkForLetterW
End If
Next cntr
If cntr = bytRemLen + 1 Then
NoNumber = True
End If
ChkForLetterW:
'check to see if the letter "W" exists
'in the remainin g string
bytWLoc = InStr(1, strRemStr, "W")
If bytWLoc > 0 Then
'read the "W" string from the string (no matter
' if it is a capital "W" or not it will still be
' the same character
strW = MID(strRemStr, bytWLoc, 1)
tmp = tmp + strW
Else
NoW = True
End If
'rule "B-2" - if there is no number following the "/" and
' there is no "W" following the "/"
'use rule "A"
If NoNumber = True And NoW = True Then
GoTo RuleA
End If
'the rules for "B" have been applied and the string is ready
GoTo ReturnString
Else
'try to find the last number in the string
bytChrLoc = bytChrLoc - 1
GoTo FindLastNum
End If
Else
tmp = pString
End If

'rule "C" - if the string contains a "-"
bytChrLoc = InStr(1, tmp, "-")
If bytChrLoc > 0 Then
strHyphen = Right(tmp, Len(tmp) - (bytChrLoc - 1))
tmp = Left(tmp, bytChrLoc - 1)
Else
strHyphen = ""
End If

RuleA:
'rule "A" - String must end with a number except when
' there is a "W" in the string
'find the last numeric value in the string
strPrevChar = Right(tmp, 1)
If IsNumeric(strPrevChar) Then
GoTo ReturnString
Else
bytRemLen = Len(tmp)
For cntr = 1 To bytRemLen
strPrevChar = MID(tmp, Len(tmp) - cntr, 1) Here's the hang point
If IsNumeric(strPrevChar) Then
tmpStr = Left(tmp, Len(tmp) - cntr)
GoTo ChkForExistingW
End If
Next cntr
End If
ChkForExistingW:
bytRemLen = Len(tmp) - Len(tmpStr)
strRemStr = Right(tmp, bytRemLen)
bytWLoc = InStr(1, strRemStr, "W")
If bytWLoc > 0 Then
'read the "W" string from the string (no matter
' if it is a capital "W" or not it will still be
' the same character
strW = MID(strRemStr, bytWLoc, 1)
tmp = tmpStr + strW
Else
tmp = tmpStr
End If
If strHyphen > "" Then
tmp = tmp + strHyphen
End If
End If

ReturnString:
fGetFirstChars_Nums_w = tmp
End Function

**June7** · 11-21-2014, 04:10 PM

Review
http://allenbrowne.com/ser-23b.html
http://allenbrowne.com/ser-23a.html

Maybe you don't need an error handler but just need another If condition.

**Haggrr** · 11-24-2014, 04:11 PM

Okay. In the only case, at present, is an item ending in 'K' is causing the problem.

**June7** · 11-24-2014, 04:17 PM

I don't know your data so no idea why that would happen. Have code to check for the 'k' and discontinue code execution or do whatever if found. That code can be in the code you currently have or in an error handler.

**ssanfu** · 11-24-2014, 04:52 PM

I executed your code using the example given: "STBK-75"
The reason you were getting an error is that the FOR...NEXT loop was attempting to go beyond the beginning of the string.
I modified the code (in blue):

Code:

RuleA:
        'rule "A" - String must end with a number except when
        ' there is a "W" in the string
        'find the last numeric value in the string
        strPrevChar = Right(tmp, 1)
        If IsNumeric(strPrevChar) Then
            GoTo ReturnString
        Else
            bytRemLen = Len(tmp)
            For cntr = 0 To bytRemLen - 1
                strPrevChar = Mid(tmp, bytRemLen - cntr, 1)      'Here's the hang point
                If IsNumeric(strPrevChar) Then
                    tmpStr = Left(tmp, bytRemLen - cntr)
                    GoTo ChkForExistingW
                End If
            Next cntr
        End If
ChkForExistingW:

It doesn't error, but I don't know if it is returning the expected value.

Since you are moving backwards through the string, a better way (IMO) to write the FOR...NEXT loop would be:

Code:

            For cntr = bytRemLen To 1 Step -1
                strPrevChar = Mid(tmp, cntr, 1)
                If IsNumeric(strPrevChar) Then
                    tmpStr = Left(tmp, cntr)
                    GoTo ChkForExistingW
                End If
            Next cntr

**Haggrr** · 11-24-2014, 05:14 PM

I have a customer that allows me to download their data into my program. From there I 'un-concatenate their strings into 'base' style numbers so I can do auto billing without having to semi-duplicate for the many variations of each style. Ex. Y1234/bt/aa. There are hundreds of variation for each base style. Those variations does change my charges or what I pay others to do the work. Instead of having 3 millions styles to do with, I only need 18,000.
Everyday I receive hundreds of new jobs to do, each assigned to a unique job number.

I am humbly asking for help in allowing exceptions like "STBK-75", when that string doesn't fit into the parameters of my module. Not to stop the module, but to simply 'ignore' that/those styles that don't fit.

**ssanfu** · 11-24-2014, 06:38 PM

Do you have the rules written down?
I see "RuleA" and rule "C".

Do you have examples of the incoming strings and what the expected return values would be?

You want "Y1234/bt/aa" to return "Y1234"?
And "STBK-75" should return "STBK-75"?

**orange** · 11-24-2014, 06:58 PM

Haggrr,

If you do not have a utility called Smart Indenter, you should get it. It makes reading your code much easier.

It's free. see this link

I've used it for years and now on Acc2010 and Windows 8.1

**Haggrr** · 11-25-2014, 04:26 PM

You want "Y1234/bt/aa" to return "Y1234"? Yes

And "STBK-75" should return "STBK-75"? That would be fine too! (Maybe somewhere in that module I could enter *wildcards* to be ignored)

**ssanfu** · 11-25-2014, 08:20 PM

To help, I would need at least 20 - 30 examples of what you have and what you want:

Example:
"Y1234/bt/aa" return "Y1234"
"STBK-75 return "STBK-75" (or what you want returned)
.
.
.
.

The more & different examples the better the result. 2 examples out of 18,000 (minimum) is not a lot of testing.

Again, Do you have the rules written down?

**Haggrr** · 11-26-2014, 01:42 PM

The problem is STBK-75 root style doesn't contain a number. The module just dies there. I have to manually add a numbers to get the module to continue.

Item	MatchString
X6431BT/AA	X6431
X6432	X6432
X6432S/AA	X6432
X6432S/AA	X6432
X6433BT/AA	X6433
X6434BT/AA	X6434
X6435	X6435
X6435GA/AA	X6435
X6440GA/AA	X6440
X6440GA/AA	X6440
X6441AM/AA	X6441
X6442AM/AA	X6442
X6442CI/AA	X6442
X6442CI/AA	X6442
X6442CI/AA	X6442
X6443	X6443
X6443GA/AA	X6443
X6443GA/AA	X6443
X6443GA/AA	X6443
X6443GA/AAA	X6443
X6443SP/AA	X6443
X6444BT/AA	X6444
X6444BT/AA	X6444
X6445TV/AA	X6445
X6446BT/AA	X6446
X6448GA/AA	X6448
X6449	X6449
X644WAA	X644W
X644WAA	X644W
X6451AM/AA	X6451
X6451BT/AA	X6451
X6451T/AA	X6451
X6451T/AA	X6451
X6451T/AA	X6451
X6452AM/AA	X6452
X6452MS/AA	X6452
X6455GA/AA	X6455
X6457BT/AA	X6457
X6457BT/AA	X6457
X6458	X6458
X6458CI/AA	X6458
X6458S/AA	X6458
X646AA	X646
X646AA	X646
X646AA	X646
X646WAA	X646W
X646WAA	X646W
X646WAA	X646W

**ssanfu** · 11-26-2014, 09:18 PM

Make sure you test the following code in a COPY of your dB!!!

'rule "B-2" - if there is no number following the "/" and
' there is no "W" following the "/"
'use rule "A"

Not sure about 'rule "B-2"..... There were no examples of a string with a number or "W" after the slash in post#11.

The function works for every example in post #11:

Code:

Public Function fGetFirstChars_Nums_w(pString As Variant) As String
    Dim tmp As String
    Dim strRemStr As String
    Dim strPrevChar As String
    Dim bytChrLoc As Byte
    Dim cntr As Integer

    Dim HasSlash As Boolean
    Dim HasHyphen As Boolean
    Dim HasW As Boolean

    Dim HasSlash As Boolean
    Dim HasHyphen As Boolean
    Dim HasW As Boolean


    'do not allow NULLS
    If IsNull(pString) Then
        'change the "??" to whatever you want
        fGetFirstChars_Nums_w = "??"
        '        fGetFirstChars_Nums_w = vbNullString   '<<-- alternative to "??"
        Exit Function
    End If

    'check for Slash, W and Hyphen
    HasSlash = InStr(pString, "/")
    HasW = InStr(pString, "W")
    HasHyphen = InStr(pString, "-")

    'Handle String with slash
    If HasSlash Then
        bytChrLoc = InStr(1, pString, "/")
        'get everythin before slash
        tmp = Left(pString, bytChrLoc - 1)
        strPrevChar = Right(tmp, 1)
        'is last char numeric?
        If IsNumeric(strPrevChar) Then
            strRemStr = tmp
        Else
            If HasW Then
                'remove everthing after the "W"
                For cntr = Len(tmp) To 1 Step -1
                    strPrevChar = Mid(tmp, cntr, 1)
                    If strPrevChar = "W" Then
                        strRemStr = Left(tmp, cntr)
                        HasW = False
                        Exit For
                    End If
                Next cntr
            Else
                'remove everthing after the last number if not "W"
                For cntr = Len(tmp) To 1 Step -1
                    strPrevChar = Mid(tmp, cntr, 1)
                    If IsNumeric(strPrevChar) Then
                        strRemStr = Left(tmp, cntr)
                        Exit For
                    End If
                Next cntr

            End If     'If HasW
        End If    'If IsNumeric(strPrevChar)
    End If    'If HasSlash

    'Handle String with "W"
    If HasW Then
        'remove everthing after the "W"
        For cntr = Len(pString) To 1 Step -1
            strPrevChar = Mid(pString, cntr, 1)
            If strPrevChar = "W" Then
                strRemStr = Left(pString, cntr)
                Exit For
            End If
        Next cntr
    End If

    'Handle String with Hyphen
    If HasHyphen Then
        'no slash and no "W" so just return pString
        strRemStr = pString
    End If

    'Handle String with no slash, no "W" and no hyphen
    ' check if last char of pString is numeric
    If Not HasSlash And Not HasW And Not HasHyphen Then
        strPrevChar = Right(pString, 1)
        'is last char numeric?
        If IsNumeric(strPrevChar) Then
            strRemStr = pString
        Else
            'not numeric, strip off letters
            For cntr = Len(pString) To 1 Step -1
                strPrevChar = Mid(pString, cntr, 1)
                If IsNumeric(strPrevChar) Then
                    strRemStr = Left(pString, cntr)
                    Exit For
                End If
            Next cntr
        End If
    End If

    'return value
    fGetFirstChars_Nums_w = strRemStr

End Function

Remember what I said about using a copy of the dB...

**Haggrr** · 11-28-2014, 11:10 AM

Other than the few 'Dim' copied twice, this worked perfectly. I don't know how you guys do what you do, but thanks. Please mark as solved!!!

**June7** · 11-28-2014, 01:12 PM

You can mark your threads as solved. Thread Tools dropdown above first post. Done.

Help the error handling.

Thread Tools

Help the error handling.

Similar Threads

Error handling of table update error?

error handling

Error Handling Question - On Error Goto

Why do I get an error in a query field when error handling is built in?

Error 2501 displays with Error handling

Posting Permissions