Search code examples
vbaoutlook

How concatenate recordset variables to use filter method - Vba

I would like to concatenate multiple variables on my recordset filter. Here more information :

  • rs is recordset
  • titre can be M. or Mme or S. (come from rs)
  • Nom is of the form (come from rs) : FirstName LastName (with space between)

but I can't. I tried :

space = " "
rs.Filter = "[titre+ space + Nom] = '" & oLookFullName & "' and nomEntreprise = '" & objContact.CompanyName & "'"

Concatenation = rs!titre + " " + rs!Nom
rs.Filter = "Concatenation = '" & oLookFullName & "'"

Any ideas ?

                                         EDIT

@Gustav, I tried your code with split and it seems the filter contains the correct value but just after I have this if loop and in this case rs.EOF is true while the contact exists... why ?

If rs.EOF Then 'Or (rs.BOF = True) Then
    Debug.Print oLookFullName & " is not found."
End If
Solution

  • Try with:

    rs.Filter = "[titre] & ' ' & [Nom] = '" & oLookFullName & "' And [nomEntreprise] = '" & objContact.CompanyName & "'"

    or:

    rs.Filter = "[titre] = '" & Split(oLookFullName, " ")(0) & "' And [Nom] = '" & Split(oLookFullName, " ")(1) & "' And [nomEntreprise] = '" & objContact.CompanyName & "'"