Multiple DoCmd.ApplyFilter filteers on form

[smaier]

Hello, all!

In short what I am trying to achieve is to filter a bound form's shown records using the DoCmd.ApplyFilter method. This is form is bound to a table that houses information on preventative maintenance actions performed on machinery. The form itself is just a review form so the records can be checked to see if they were done and when.



I have 3 controls in the form I am trying to use as filters:

MachineComboBox
StartDateTextBox
EndDateTextBox

If I try a single filter (filtering by machine number) using the following code it works fine:

DoCmd.ApplyFilter , "[Machine]='" & Me.MachineComboBox & "'"

However, when I try to filter by machine number as well as by date range (the values between StartDateTextBox and EndDateTextBox) I get a syntax error:

DoCmd.ApplyFilter , "[Machine]='" & Me.MachineComboBox & "'"
DoCmd.ApplyFilter , "[PerformedOn] >='" & Me.StartDateTextBox & "'" and <='" & Me.EndDateTextBox & "'"

So my questions are, is trying to perform two filters in this way the wrong way to go about it, and in the second line of above code where am I going wrong. Admittedly (and obviously), I am horrible at using single and double quotes properly.

Thank you!


Edit: The textboxes are formatted as Short Date and the table column is formatted as Date/Time.

[pbaldy]

Try

DoCmd.ApplyFilter , "[PerformedOn] >=#" & Me.StartDateTextBox & "# and [PerformedOn] <= #" & Me.EndDateTextBox & "#"

[ranman256]

sub btnFind_click()
dim sWhere as string


sWhere = "1=1"
if not IsNull(txtBox1) then sWhere = sWhere & " and [field1]='" & me.txtBox1 & "'"
if not IsNull(txtBox2) then sWhere = sWhere & " and [field2]='" & me.txtBox2 & "'"
if not IsNull(cboBox) then sWhere = sWhere & " and [field3]='" & me.cboBox & "'"


if sWhere = "1=1" then
   me.filterOn = false
else
   me.filter = sWhere
   me.filterOn = true
endif

[smaier]

pbaldy;

Worked like a charm, thank you so much! I've used the pound sign for dates in queries before. Don't know why it didn't occur to me use them in VBA. Not to excuse my lack of proficiency with usage of quotes. Very much appreciated

ranman256;

That's a really creative solution. I'm still working on figuring out my error in adapting it (I'm getting data type mismatch error), but I want to figure it out since it's a different approach than I would have thought of.


Thank you very much, all!

[pbaldy]

Happy to help!

To help with ranman's solution, you can use this to see the finished string:

http://www.baldyweb.com/ImmediateWindow.htm

The data type mismatch typically means you've used the wrong delimiters. Generally, surround date values with #, text values with ', numeric values with no delimiters.

Edit: It would also help to see your code.