Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have the a myRegex function to extract Regexes from a string. When I run a query that uses the function I get the following error on multiline.

Method or data member not found.

This is the regex Function:

Function myRegex(ByRef myString As String, ByVal pattern As String) As String
   Dim rgx As New RegExp
    Dim colMatches As MatchCollection
    With rgx
        .pattern = pattern
        .ignoreCase = True
        .Global = False
        .Multiline = False
        Set colMatches = .Execute(myString)
    End With
    If colMatches.Count > 0 Then
        myRegex = colMatches(0).Value
        myRegex = ""
    End If
End Function 
This is the query I used:
SELECT myRegex(phone,"[0-9]+")
FROM table1
I have the following reference libraries checked:
Microsoft VBScript Regular Expressions 1.0 
Microsoft VBScript Regular Expressions 5.5
...matches RegExp with the first library that defines that Class, which is 
Microsoft VBScript Regular Expressions 1.0
This is an older version that does not support the Multiline property. You need the RegExp class from:
Microsoft VBScript Regular Expressions 5.5
So either:
Remove the link with that older 1.0 reference library, or
Qualify the RegExp class as VBScript_RegExp_55.RegExp, or
Use late binding (slower), with CreateObject("VBScript.RegExp")
You can uncheck the first VBScript Regular Expressions reference ... the 1.0 version ... as @trincot suggests.
Or you could uncheck both references and use late binding:
'Dim rgx As New RegExp
Dim rgx As Object
Set rgx = CreateObject("VBScript.RegExp")
However, since your query will call the function repeatedly, you may notice better performance with a Static object variable.  
Function myRegex(ByRef myString As String, ByVal pattern As String) As String
    Static rgx As Object
    Dim colMatches As Object
    If rgx Is Nothing Then
        ' create the RegExp object just once
        Set rgx = CreateObject("VBScript.RegExp")
        With rgx
            .ignoreCase = True
            .Global = False
            .Multiline = False
        End With
    End If
    rgx.pattern = pattern
    Set colMatches = rgx.Execute(myString)
    If colMatches.Count > 0 Then
        myRegex = colMatches(0).Value
        myRegex = ""
    End If
End Function 
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.