Microsoft VBS Regex Access Query

I am trying to replace a SSN from a text string stored in MS Access Query column. The comment field is a free text and I need to replace the standard SSN (i.e. 123-45-6789) ,when present, with XXX-XX-XXXX.

For instance, I would like "JOHN DOE,111-11-1111 REWARD 6 DOL has been generated. Submission selection should be completed by 07-FEB-35 PLEASE Comply" to become "JOHN DOE,XXX-XX-XXXX REWARD 6 DOL has been generated. Submission selection should be completed by 07-FEB-35 PLEASE Comply"

I used a Public function within the replace function to accomplish the task but my patter is not working as desired . Access querying criteria and RegexMathc function:

Expr1: Replace([Comments],RegexMatch([Comments],"\d{3}\-\d{2}\-\d{4}"),"XXX-XX-XXXX")

Public Function RegexMatch(value As Variant, pattern As String) As Boolean
    If IsNull(value) Then Exit Function
    ' Using a static, we avoid re-creating the same regex object for every call '
    Static regex As Object
    ' Initialise the Regex object '
    If regex Is Nothing Then
        Set regex = CreateObject("vbscript.regexp")
        With regex
            .Global = False
            .IgnoreCase = False
            .Multiline = False
            
        End With
    End If
    ' Update the regex pattern if it has changed since last time we were called '
    If regex.pattern <> pattern Then regex.pattern = pattern
    ' Test the value against the pattern '
    RegexMatch = regex.Test(value)
End Function

When I try the regex pattern in regex101 it finds the SSN string without a problem but it doesn't in my access query column.

Solution

Your RegexMatch returns a boolean, either True or False.

That means, Replace is called something like this:

Replace("JOHN DOE,111-11-1111, True reward", True, "XXX-XX-XXXX")

The following would be returned:

"JOHN DOE,111-11-1111, XXX-XX-XXXX reward"

You need to do matching and replacing in a single function:

Public Function RegexReplace(value As Variant, Pattern As String, Replace As String) As String
    If IsNull(value) Then Exit Function
    ' Using a static, we avoid re-creating the same regex object for every call '
    Static regex As Object
    ' Initialise the Regex object '
    If regex Is Nothing Then
        Set regex = CreateObject("vbscript.regexp")
        With regex
            .Global = True 'We want to replace all occurrences
            .IgnoreCase = False
            .MultiLine = False
            
        End With
    End If
    ' Update the regex pattern if it has changed since last time we were called '
    If regex.Pattern <> Pattern Then regex.Pattern = Pattern
    ' Replace using the pattern
    RegexReplace = regex.Replace(value, Replace)
End Function