DoCmd.AppltFilter with multiple criteria overwriting

1
votes

I have an MS.Access form with 2 filters, one filters between dates using text boxes

DoCmd.ApplyFilter , "[Start Date] Between #" & Format([AVstrtdt], "yyyy\/mm\/dd") & "# And #" & Format([AVEnDt], "yyyy\/mm\/dd") & "#"

And the other filters by department using a combo box..

DoCmd.ApplyFilter "", "[OpDescription]=" & ([CmbBxDepart]) & ""

Both work perfectly on there own but they overwrite each other IE the date range overwrites the department filter or the department filter overwrites the date filter so I've been trying to stick them together as one filter using the Or function

But keep getting a type mismatch error...

Private Sub ApplyDtFilt_Click()

DoCmd.ApplyFilter , "[Start Date] Between #" & Format([AVstrtdt], "yyyy\/mm\/dd") & "# And #" & Format([AVEnDt], "yyyy\/mm\/dd") & "#" Or "", "[OpDescription]=" & ([CmbBxDepart]) & ""

End Sub

I've searched and found similar posts but I cant seem to find what's wrong... any ideas? looks ok to me..

Thanks..

1
vbams-accessfilter

1 Answers

1
votes

Use one expression - as a WHERE clause in SQL:

DoCmd.ApplyFilter , "([Start Date] Between #" & Format([AVstrtdt], "yyyy\/mm\/dd") & "# And #" & Format([AVEnDt], "yyyy\/mm\/dd") & "#) Or ([OpDescription]=" & [CmbBxDepart] & ")"

Alternatively, set the Filter property of the form:

Dim Filter As String

Filter = "([Start Date] Between #" & Format([AVstrtdt], "yyyy\/mm\/dd") & "# And #" & Format([AVEnDt], "yyyy\/mm\/dd") & "#) Or ([OpDescription]=" & [CmbBxDepart] & ")"
Me.Filter = Filter
Me.FilterOn = True