title: Refactoring sql into regexp - Microsoft Access
body: <p>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? </p>
  
  <p>By the way, this query oddly enough doesn't work fully and maybe it's why this could use regexp.</p>
  
  <p>The idea is to match the three fields in the temp_table query:</p>
  
  <pre><code>last_name || first_name || middle_initial
  Blair     || Sheron     || S
  Brown     || Wanda      || R
  Rodriguez || Lillian    || M
  Glaubman  || Alan       || 
  </code></pre>
  
  <p>with what is in either names_1 or names_2 field in print_ready:</p>
  
  <pre><code>BLAIR,SHERON S
  BROWN,BRENON I H/E BROWN,WANDA R 
  RODRIGUEZ,LILLIAN M
  GLAUBMAN,ALAN &amp; SHORSTEIN,LILLIAN 
  </code></pre>
  
  <p>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.</p>
  
  <p>And it still gives me user type undefined for " colMatches As MatchCollection" </p>
  
  <pre><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) &gt; 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");
  </code></pre>