I have a problem when filtering this acces database, this is what i have found so far:
1. when i do it with one criteria it works
2. when i concatenate two clauses with direct values it works
3. but when i assign a variable instead of direct values it does not work!!

i do not know what i am doing wrong, i have tried to work around it but i get the same problem. the error that i get is tpe mismatch, but i can't see it.

below is the code (just the portion of it)

thanks in advance

datefilter = txtFilter_Day.Text
namefilter = cmbFilter_name.Text

rstRecordset.Filter = "Date='05/24/05' AND Name='Wiliam'"  'this works
rstRecordset.Filter = ("Date='" & datefilter & "'")                'this works

rstRecordset.Filter = ("Date='" & datefilter & "'") AND ("Name='" & namefilter & "'")  'this does not work

rstRecordset.Filter = "Date='" & datefilter & "'" And "Name='" & namefilter & "'" 'this does not work

Well for one thing, on your very last line, your "and" is being referenced as a visual basic keyword, and NOT a filter query.

rstRecordset.Filter = "Date='" & datefilter & "'" And "Name='" & namefilter & "'"

you never put and back in a string, something like:

rstRecordset.Filter = "Date='" & datefilter & "' And Name='" & namefilter & "'"

might be closer to what you are looking for ;) (the same principle about AND, being outside of the string, applies to the first query that doesn't work also)

This article has been dead for over six months. Start a new discussion instead.