> Refactoring sql into regexp - Microsoft Access

[johnmerlino]

Hey all,

Any idea how to use a function to do a RegExp Replace and then calling it from the query in Microsoft Access?

The idea is to match the three fields in the temp_table query:

last_name || first_name || middle_initial
Blair     || Sheron     || S
Brown     || Wanda      || R
Rodriguez || Lillian    || M
Glaubman  || Alan       ||
Smith        || John        || L

with what is in either names_1 or names_2 field in print_ready:

BLAIR,SHERON S
BROWN,BRENON I H/E BROWN,WANDA R 
RODRIGUEZ,LILLIAN M
GLAUBMAN,ALAN & SHORSTEIN,LILLIAN 
Smith John L REL

As you can see, names_1 and names_2 may have more names than what I need (as shown in the second item in the list above) so I must use Like. And if there's a value in middle_initial column, then I need to check print_ready for that middle initial as well, otherwise I don't check for middle_initial. This ensures if there's a middle initial, it doesnt't return records with first and last name similarities, but only returns the record with the initial as well.

I put this in a VBA module:

Public Function NameMatch(ByVal pLast As String, ByVal pFirst As String, ByVal pMiddle As Variant, ByVal pSearchField As String) As Boolean

 
    Dim strReturn As String

    Dim objMatch As Match
    Dim colMatches As MatchCollection
    Dim RetStr As String

    strReturn = "/(\s|[pLast])(\s|,)[pFirst]/"

       If Len(pMiddle) > 0 Then
        strReturn = "/(\s|[pLast])(\s|,)[pFirst]\[spMiddle]/"
    End If

    Set objRegExp = New RegExp
    objRegExp.Pattern = strReturn
    objRegExp.IgnoreCase = True
    objRegExp.Global = True

    strReturn = objRegExp.Test(pSearchField)
    
    If (objRegExp.Test(pSearchField) = True) Then
    Set colMatches = objRegExp.Execute(pSearchField)
    
    Set oMatch = colMatches(0)

 
    RetStr = RetStr & oMatch.SubMatches(0)
    RetStr = RetStr & oMatch.SubMatches(1)
    NameString = RetStr
    
    End If

End Function

and then I put this in a query in access:

SELECT
  t.last_name,
  t.first_name,
  t.middle_initial,
  p.names_1,
  p.names_2
FROM temp_query AS t,
print_ready AS p
WHERE
NameMatch(t.last_name, t.first_name, t.middle_initial, p.names_1) = True
Or NameMatch(t.last_name, t.first_name, t.middle_initial, p.names_2) = True
And p.us_states_and_canada In ("FL", "NY");

and I get an error that says:

Undefined function NameMatch

So I go to VBA and select debug > compile and it says:

user-define type not defined