Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Posts
    13

    Unanswered: Refactoring sql into regexp - Microsoft Access

    I put this in a VBA module:
    Code:
    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
    and then I put this in a query in access:
    Code:
    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:
    Code:
    Undefined function NameMatch
    So I go to VBA and select debug > compile and it says:
    Code:
    user type undefined for " colMatches As MatchCollection"

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Moved to Access forum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to set a reference to Microsoft VBScript Regular Expressions
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •