asked on

MS Access: Expanding the scope of an existing Regex Function

I recently received help with a Regex function that is working well, however I would like to refine it so that it runs more efficiently on over 2mm records.
The existing function matches Codes from T_BusinessCodes with business names in T_MMA and updates field BizCd in T_MMA as 'Corp' if true.

Rather than have the function review each record for each code, I realized there are different categories of codes that can be applied with more certainty than others. By updating the codes based on category, we can eliminate those records from the next pass-through of the function and have also assigned a category for further analysis.

I have added the field: 'Category' in T_BusinessCodes which contains 5 categories:

Corp2

Corp

Geo

Proper2

Proper

I would like to run the function separately for each category (in the order noted)

on all records where the 'BizCd' field is Null

and update the 'BizCd' field with the category name.

The updated T_BusinessCodes table is attached as an Excel file: T_BusinessCodes.xlsx
The SQL for the query that calls the the RegexMatch function:

UPDATE T_MMA SET 
WHERE (((T_MMA.CDT_BUSINESS_CLASSIFICATION_CD) Like "*Ind*") AND ((T_MMA.BizCd) Is Null) AND ((RegexMatch([T_MMA].[BUSINESS_NAME_TX]))=True));

Open in new window

The 'update-to' value in the query should reflect the category that the function is processing.

The current Regex Function:


Public Function RegexMatch(ByVal parmBusinessName As Variant) As Boolean
    Static oRE As Object
    Dim rs As Recordset
    Dim strCodes As String
    
    If oRE Is Nothing Then              'only do this once in the life of the open database
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = True               'look at all possible matches
        oRE.Pattern = "([.()+])"        'characters that need escaping
        
        'clean up and concatenate the codes
        Set rs = DBEngine(0)(0).OpenRecordset("Select Trim(Code) As trim_code from [T_BusinessCodes]")
        Do Until rs.EOF
        
        'Replace is escaping the characters with a backslash
        strCodes = strCodes & "|" & oRE.Replace(rs![trim_code], "\$1")
          
          rs.MoveNext
        
        Loop
        
        oRE.Global = False                      'stop looking with the first match
        oRE.ignorecase = True
        strCodes = Mid(strCodes, 2)                  'skip the leading pipe character
        strCodes = " (?:" & strCodes & ")[ .,;]"     'put finishing touches on the pattern
        oRE.Pattern = strCodes
    End If
    
    If IsNull(parmBusinessName) Then
        RegexMatch = False
        Exit Function
    End If
    
    'prepend and append a space to the business name and invoke the
    'regex .test() method
    RegexMatch = oRE.test(" " & parmBusinessName & " ")

End Function

MS Access: Expanding the scope of an existing Regex Function

More Data Analysis