Multiple Where condition in Apply Filter command

[richard]

Hi

I have a form based on a query. I wish to apply more than one filter to the
form.I am using the docmd.applyfilter event. I have created two filters
strcriteria and strcriteria2. If I apply these seperately they work fine, if
I use then consecutively then only the latter applies. I have tried the code
below but I get an error message saying 'Type Mismatch'
Can someone take a look and give me a way around this problem
Workdate is a date field, so when got error message first time I converted
to a number to see if this solved the problem, actual date is 1/11/2009

Dim strCriteria As String
Dim strCriteria2 As String

strCriteria = "recordinvoiced=" & 0 & ""
strCriteria2 = "Workdate > " & 40118 & ""

DoCmd.ApplyFilter , strCriteria2 And strCriteria


Thanks

Richard

 

[Daryl S]

Richard -

Try this:

DoCmd.ApplyFilter , strCriteria2 & " And " & strCriteria

 

[John W. Vinson]

Hi

I have a form based on a query. I wish to apply more than one filter to the
form.I am using the docmd.applyfilter event. I have created two filters
strcriteria and strcriteria2. If I apply these seperately they work fine, if
I use then consecutively then only the latter applies. I have tried the code
below but I get an error message saying 'Type Mismatch'
Can someone take a look and give me a way around this problem
Workdate is a date field, so when got error message first time I converted
to a number to see if this solved the problem, actual date is 1/11/2009

Dim strCriteria As String
Dim strCriteria2 As String

strCriteria = "recordinvoiced=" & 0 & ""
strCriteria2 = "Workdate > " & 40118 & ""

DoCmd.ApplyFilter , strCriteria2 And strCriteria


Thanks

Richard

The Filter property needs to be a single string which contains a valid SQL
WHERE clause. If you open a multi-criteria query in SQL view you can see that
it can contain multiple conditions. It's a statement in Boolean algebra -
expressions joined by the operators AND and OR, just as a statement in
mathematical algebra uses + and - operators. If this multiple-criteria
operation evaluates to TRUE the record is displayed; if it's FALSE, not.

So your filter clause needs to end up resembling:

[recordinvoiced] = 0 AND Workdate > 40118

if those are in fact the criteria you want (though 40118 doesn't look much
like a date to me, and will probably fail if Workdate is a Date/Time field).

If you're coming up with these two strCriteria strings, you need to cobble
them together into a bigger string including the characters Blank, A, N, D,
and Blank to construct a syntactically correct string. See Daryl's response
for how to do so... just thought an explanation of why you need to do it that
way would help.