Refactoring sql into regexp - Microsoft Access

Question

I'm not a pro with regexp and especially in this specific context. Any idea how to use a function to do a RegExp Replace and then calling it from the query in Microsoft Access?

By the way, this query oddly enough doesn't work fully and maybe it's why this could use regexp.

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       || 

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 

For some strange reason, my query fails to return the four names above, even though they are obviously in the temp_table, as shown above. The trick though is that names_1 and names_2 will 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.

And it still gives me user type undefined for " colMatches As MatchCollection"

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 colMatches As MatchCollection
Dim RetStr As String
Dim objRegExp As RegExp

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

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

For Each match In colMatches
    record += match.Value
Next

NameString = record

End If

End Function

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");

Answer 1

Since your query will run within Access, the expression service will be available to allow the query to use a VBA user-defined function.

Public Function NameString(ByVal pLast As String, _
    ByVal pFirst As String, _
    ByVal pMiddle As Variant) As String

    Dim strReturn As String
    strReturn = pLast & "," & pFirst
    If Len(pMiddle & "") > 0 Then
        strReturn = strReturn & " " & pMiddle
    End If
    NameString = strReturn
End Function

Then your query can look for matching name patterns in the print_ready target fields.

SELECT
    t.last_name,
    t.first_name,
    t.middle_initial,
    t.Name2Search,
    p.names_1,
    p.names_2
FROM
    (SELECT
        last_name,
        first_name,
        middle_initial,
        NameString(last_name, first_name, middle_initial) AS Name2Search
    FROM temp_table
    ) AS t,
    print_ready AS p
WHERE
    (p.names_1 Like "*" & t.Name2Search & "*")
    Or (p.names_2 Like "*" & t.Name2Search & "*");

Update: I overlooked your us_states_and_canada restriction. Add an "AND" condition to your WHERE clause.

p.us_states_and_canada In ("FL", "NY")

Answer 2

I don't understand RegExp, but looking at @HansUp's answer, this seems entirely equivalent:

  SELECT last_name, first_name, middle_initial, names_1, names_2,
    Mid(("12"+last_name) & (", "+first_name)), 3) 
      & (" "+middle_initial) AS Name2Search
  FROM print_ready
  WHERE
    (names_1 Like "*" & Mid(("12"+last_name) & (", "+first_name)), 3) 
      & (" "+middle_initial) & "*")
    Or (names_2 Like "*" & Mid(("12"+last_name) & (", "+first_name)), 3) 
      & (" "+middle_initial) & "*");

But I may very well be completely misinterpreting what's being sought.

There's no really good way to do this. I would have a look at the data in names_1 and names_2 and see if it couldn't be parsed into indexable fields that could be compared to last_name and first_name and take advantage of those indexes.

Answer 3

Try this NameMatch function.

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

    Dim strPattern As String
    Dim objRegExp As Object

    ' .* = any characters, or none '
    ' \b = word boundary '
    strPattern = ".*\b" & pLast
    ' comma, space, or comma plus space '
    strPattern = strPattern & "(,|\s|,\s)"
    strPattern = strPattern & pFirst
    If Len(pMiddle & vbNullString) > 0 Then
        strPattern = strPattern & "\s" & pMiddle
    End If
    strPattern = strPattern & "\b.*"
    'Debug.Print strPattern '
    Set objRegExp = CreateObject("VBScript.RegExp")
    objRegExp.Pattern = strPattern
    objRegExp.IgnoreCase = True

    NameMatch = objRegExp.test(pSearchField)
    Set objRegExp = Nothing
End Function

Here is a copy & paste from the Immediate Window where I tested the function:

? NameMatch("Doe","John","A","foo Doe, John A bar")
True
? NameMatch("Doe","John","A","foo Doe,John A bar")
True
? NameMatch("Doe","John","A","foo Doe John A bar")
True
? NameMatch("Doe","John","A","foo Doe, John A")
True
? NameMatch("Doe","John","A","Doe, John A")
True
? NameMatch("Doe","John","A","foo Doe, John bar")
False
? NameMatch("Doe","John",Null,"foo Doe, John bar")
True
? NameMatch("Doe","John",Null,"foo Doe, John A bar")
True

Except for the last one, I think those are the results you wanted. But I don't know about the last one.