Assigning wildcard matches to a variable

[RustyShackleford]

I'm building an Access 2010 database where I need to map imported records to a destination using rules that sit in a static reference table. In some cases, the static reference will use wildcards (both * and ?) to determine the destination. For example:

Take these import records:
Asset12345
Liability1012300

And map them to the following destinations:
Asset12345 -> CurrentAsset123
Liability1012300 -> CurrentLiability123

Using these two rules defined in a static reference table:
Asset*45 -> CurrentAsset*


Liability10???00 -> CurrentLiability???

Essentially, I'm asking whether it's possible to take the characters that are "matched" to the wildcards and assign them to a variable which can be used elsewhere. I've looked for answers to this online and played around for several hours in Access but I can't seem to store the characters that match the wildcard. Any ideas?

[orange]

I'm sure it can,but you should be absolutely clear on the logic of the reassignment code.
eg
Asset*45 represents any string beginning "Asset" - followed by 0 or more occurrences of any characters - ending with "45"
Liability10???00 represents any string beginning "Liabiliity10" - followed by any 3 characters - ending with "00"

If this is dealing with finances/accounts, you should be testing thoroughly.

Post back and give us some details.

[RustyShackleford]

Yes, that's correct. The rules that reside in the reference table can have any combination of wildcard characters and regular characters. I understand that * can take the place of any string and a ? takes the place of a single character. Here is another example if that helps to clarify:

Rule:
Segment??Product* -> Product??*

Result:
SegmentABProduct_1987 would be mapped to ProductAB_1987
SegmentProductSurfBoards would be mapped to ProductSurfBoards

The first example I gave used finance terms but the database is used for research, and is not used to process any kind of financial data.

[orange]

I don't think your second example is correct.
SegmentProductSurfBoards would be mapped to ProductSurfBoards

By your rule

Rule:
Segment??Product* -> Product??*

Segment must be followed by 2 chars.

from m$oft:

Characters in pattern	Matches in string
?	Any single character
*	Zero or more characters
#	Any single digit (0–9)

[RustyShackleford]

You're right, my last example would work if the rule were Segment*Product*-> Product**, not Segment??Product*-> Product??*.

So, any thoughts on how to get this to work in Access? I've been trying to find out how to assign the matched characters to a variable but have not had any luck.

[orange]

Here is a function that you can call to do your matches and transformations.
It deals with the Rule Segment??Product* -> Product??* where ?? can be 0 or more characters (our definition not m$oft).

You have to set up the parameters as shown in the function and the test routine. You can call this from a query as needed.

'---------------------------------------------------------------------------------------
' Procedure : WildcardMatch
' Author    : Jack
' Date      : 19/03/2014
' Purpose   : To parse a string looking for patternA, then find patternB, concatenate the character
'             between patternA and PatterB to the desired output base.
'
'                                 ** Parameters **
'                       sStringToChange  ===the original/old code
'                       sPatternA        ===the leftmost / first search pattern
'                       sPatternB        ===the rightmost / second search pattern
'                       sOutputBase      ===the new base to attached parsed characters to
'
'                                    ** The Rule **
'
'                            Rule:
'                            Segment??Product* -> Product??*
'
'  from  https://www.accessforums.net/programming/assigning-wildcard-matches-variable-42411.html#post217910
'---------------------------------------------------------------------------------------
'
Function WildcardMatch(sStringToChange As String, _
                       sPatternA As String, _
                       sPatternB As String, _
                       sOutputBase As String) As String
          Dim s1 As String
          Dim s3 As String
            
          Dim myOutputBase As String
          Dim sOutput As String
          Dim IEndPatA As Integer  'end of PatternA
          Dim IEndPatB As Integer  'end of PatternB
          Dim iPos1 As Integer
          Dim iPos2 As Integer
10        On Error GoTo WildcardMatch_Error

20        myOutputBase = sOutputBase
30        myPatternA = sPatternA
40        myPatternB = sPatternB
               's1 = "testSegmentXYProduct1234duct1234"  'test
               's1 = "SegmentProductSurfBoards"             'test
               's1 = "SegmentbbbbbbbbProductSurfBoards"
50        s1 = sStringToChange
60        iPos1 = InStr(s1, sPatternA)
70        If iPos1 > 0 Then
80            IEndPatA = iPos1 + Len(sPatternA)
90        End If

100       If IEndPatA > 0 Then                       'found PatternA now check for PatternB
110           iPos2 = InStr(s1, sPatternB)
120           If iPos2 > 0 Then
130               If iPos2 = IEndPatA Then  'nothing between PatternA and PatternB
140                   IEndPatB = iPos2 + Len(sPatternB)
150                   s3 = Mid(s1, IEndPatB)           'this ok  go to output
160               Else
170                   s3 = Mid(s1, IEndPatA, iPos2 - IEndPatA)
180               End If
190           End If
             ' Debug.Print "s3 " & s3                     'debugging
200           sOutput = myOutputBase & s3
              ''''''Debug.Print sOutput                    'testing
              '
              ' return the converted string
              '
210           WildcardMatch = sOutput
220       End If

230       On Error GoTo 0
240       Exit Function

WildcardMatch_Error:

250       MsgBox "Error " & Err.number & " on line " & Erl & " (" & Err.Description & ") in procedure WildcardMatch of Module Module1"
End Function

Here is a test routine to show how it operates. You can use different values of s1 to see the results.

Sub testWildCard()
    Dim s1 As String
    Dim s2 As String
    Dim s3 As String
    Dim s4 As String
    's1 = "testSegmentXYProduct1234duct1234"  'test
    s1 = "SegmentProductSurfBoards"             'test
    's1 = "SegmentbbbbbbbbProductSurfBoards"
    s2 = "Segment"
    s3 = "Product"
    s4 = "Product"
    Debug.Print WildcardMatch(s1, s2, s3, s4)
End Sub

Sample output from testWildCard is

ProductSurfBoards

Good luck. If it needs to be adjusted, post back.

[orange]

There is a logic issue in previous code.
Working on a function to help with parsing. Will post back later.

Here is revised function code and Test program and results from running each of the input test strings. This deals with any characters beyond PatternB in the original data.

'----------------------------------------------------------------------------------------------
' Procedure : WildcardMatch
' Author    : Jack
' Date      : 19/03/2014
' Purpose   : To parse a string looking for patternA, then find patternB, concatenate the character
'             between patternA and PatterB to the desired output base, as well as any characters beyond PatternB in the input
'             string.
'
'                                 ** Parameters **
'                       sStringToChange  ===the original/old code
'                       sPatternA        ===the leftmost / first search pattern
'                       sPatternB        ===the rightmost / second search pattern
'                       sOutputBase      ===the new base to attached parsed characters to
'
'                                    ** The Rule **
'
'                            Rule:
'                            Segment??Product* -> Product??*
'     NOTE: ***
'           ***     Here the ?? represents 0 or more characters for our purposes. This is NOT
'           ***     the ? single character used by M$oft.
'           ***
'           *************************************************************************************
'
'  from  https://www.accessforums.net/programming/assigning-wildcard-matches-variable-42411.html#post217910
'---------------------------------------------------------------------------------------
'
Function WildcardMatch(sStringToChange As String, _
                       sPatternA As String, _
                       sPatternB As String, _
                       sOutputBase As String) As String
          Dim s1 As String  'incoming string to check
          Dim s3 As String  'text between PatternA and PatternB
          Dim s4 As String  'text post patternB
          Dim myOutputBase As String
          Dim sOutput As String
          Dim IEndPatA As Integer  'end of PatternA
          Dim IEndPatB As Integer  'end of PatternB
          Dim iPos1 As Integer
          Dim iPos2 As Integer
10        On Error GoTo WildcardMatch_Error

20        myOutputBase = sOutputBase
30        myPatternA = sPatternA
40        myPatternB = sPatternB

50        s1 = sStringToChange
60        iPos1 = InStr(s1, sPatternA)
70        If iPos1 > 0 Then                            'found PatternA
80            IEndPatA = iPos1 + Len(sPatternA)


              'found PatternA now check for PatternB
90            iPos2 = InStr(s1, sPatternB)
100           If iPos2 > 0 Then                          'patternB found
110               If iPos2 = IEndPatA Then               'nothing between PatternA and PatternB
120                   s3 = ""                            'this ok  go to output
130               Else
140                   s3 = Mid(s1, IEndPatA, iPos2 - IEndPatA)  'between Patterns
150               End If
160               IEndPatB = iPos2 + Len(sPatternB)
                  'check if anything is beyond PatternB
170               If Len(s1) > IEndPatB Then
180                   s4 = Mid(s1, IEndPatB)
190               Else
200                   s4 = ""
210               End If

                  ' Debug.Print "s3 " & s3                     'debugging
220               sOutput = myOutputBase & s3 & s4
                  ''''''Debug.Print sOutput                    'testing
230           End If    '

240       Else
250           sOutput = ""     'nothing to return
260       End If
          ' return the converted string
          '
270       WildcardMatch = sOutput
280       On Error GoTo 0
290       Exit Function

WildcardMatch_Error:

300       MsgBox "Error " & Err.number & " on line " & Erl & " (" & Err.Description & ") in procedure WildcardMatch of Module Module1"
End Function

Here is the test procedure showing how to call the function.
The test results (below) were the result of running each test string.

 Sub testWildCard()
    Dim s1 As String
    Dim s2 As String
    Dim s3 As String
    Dim s4 As String
    's1 = "testSegmentXYProduct1234duct1234"  'test
   's1 = "SegmentProductSurfBoards"             'test
     's1 = "SegmentbbbbbbbbProductSurfBoards"
     s1 = "ThishasNO pattern and returns an empty string"
    s2 = "Segment"
    s3 = "Product"
    s4 = "Product"
    Debug.Print IIf((WildcardMatch(s1, s2, s3, s4) = ""), "NullString Returned", WildcardMatch(s1, s2, s3, s4))
End Sub

Test Results:

ProductXY1234duct1234
ProductSurfBoards
ProductbbbbbbbbSurfBoards
NullString Returned

Good luck.