filter multiple criteria with "or"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to have a command button look up based on 2 criteria based on
first & last names. Works fine if I'm doing one or the other:

Dim FIRSTNAMEEXPR As Variant
Dim LASTNAMEEXPR As Variant

(the FIRSTNAMEEXPR and LASTNAMEEXPR are expressions that I have (work fine)
that have pared down names to get rid of middle initials, Jr., Sr. etc.... I
didn't put whole thing here)

Me.Filter = "[first] Like'" & FIRSTNAMEEXPR & "'" And "[last] Like'" &
LASTNAMEEXPR & "'"
Me.FilterOn = True


also, is there a way to do "Or"?

Many thanks!
 
Hi, tgl.

AND needs to be part of your string expression that you set the filter to.
Rearrange your quotes so that it is cast as a string literal:

Me.Filter = "[First] Like'" & FIRSTNAMEEXPR & "' And [Last] Like'" & _
LASTNAMEEXPR & "'"
Me.FilterOn = True

Hope that helps.
Sprinks
 
I'm trying to have a command button look up based on 2 criteria based on
first & last names. Works fine if I'm doing one or the other:

Dim FIRSTNAMEEXPR As Variant
Dim LASTNAMEEXPR As Variant

(the FIRSTNAMEEXPR and LASTNAMEEXPR are expressions that I have (work fine)
that have pared down names to get rid of middle initials, Jr., Sr. etc.... I
didn't put whole thing here)

Me.Filter = "[first] Like'" & FIRSTNAMEEXPR & "'" And "[last] Like'" &
LASTNAMEEXPR & "'"
Me.FilterOn = True

also, is there a way to do "Or"?

Many thanks!
Click to expand...

Too many double quotes.
I've added a space between the single and double quotes for clarity.
Remove the spaces when using the code. Also add a space after Like and
the ' so Access recognizes the word.

Me.Filter = "[first] Like ' " & FIRSTNAMEEXPR & " ' And [last] Like
' " & LASTNAMEEXPR & " ' "
Me.FilterOn = True
 
Back
Top