Multiple Where condition in Apply Filter command
in [General]
|
Prev: 2 Gig limit on an Access DB will fill up very quickly when using attachment data type
Next: duportail
From: richard on 30 Mar 2010 08:21 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
From: Daryl S on 30 Mar 2010 09:11 Richard - Try this: DoCmd.ApplyFilter , strCriteria2 & " And " & strCriteria -- Daryl S "richard" wrote: > 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
From: John W. Vinson on 30 Mar 2010 11:40
On Tue, 30 Mar 2010 05:21:02 -0700, richard <richard(a)discussions.microsoft.com> wrote: >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. -- John W. Vinson [MVP] |