We help IT Professionals succeed at work.
Get Started
Troubleshooting Question

MS Access: Expanding the scope of an existing Regex Function

88 Views
Last Modified: 2021-01-04
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));

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




Comment
Watch Question
Social distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014
Commented:
This problem has been solved!
Unlock 1 Answer and 51 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE