VBA to Apply/Remove Filters (1 Viewer)

bmcgree1

Registered User.
Local time
Yesterday, 22:50
Joined
Jun 19, 2009
Messages
43
Okay so 2 problems, first I want to apply a filter to a bound form that shows the dates that lie within a specific range. That range is the current year, current month and any day. I can't figure out the correct VBA for it, below is what I have.

DoCmd.ApplyFilter , "[DateEnd] = Between DateSerial(Year(Now()),Month(Now()),1 And DateSerial(Year(Now()),Month(Now()),31"
My second problem is also with filters, I have a button on my form that asks the user to enter a customer name to filter by. A customer name is entered and the filter works fine. But after I toggle to filter off and want to enter a different customer name access seems to keep what I entered first in its memory and will just filter on what value only. I get no prompts to enter a different value, it just holds onto the first value until I close and reopen the form.

How do I fix these 2 things?!?! Any help is very appreciated. Thanks!
 

SOS

Registered Lunatic
Local time
Yesterday, 22:50
Joined
Aug 27, 2008
Messages
3,517
Don't use DoCmd.ApplyFilter.

Use

Me.Filter = "[DateEnd] Between #" & DateSerial(Year(Date()),Month(Date()),1) & "# And #" & DateSerial(Year(Date()),Month(Date()),31) & "#"
Me.FilterOn = True

And to clear

Me.Filter = ""
Me.FilterOn = False
 

SOS

Registered Lunatic
Local time
Yesterday, 22:50
Joined
Aug 27, 2008
Messages
3,517
And your original code here was missing some parentheses, should use DATE and not NOW (which has a time component) for the date, and you do not use = before the BETWEEN.
 

bmcgree1

Registered User.
Local time
Yesterday, 22:50
Joined
Jun 19, 2009
Messages
43
SOS thank you very much, this works great!
 

bmcgree1

Registered User.
Local time
Yesterday, 22:50
Joined
Jun 19, 2009
Messages
43
Maybe I spoke too soon, everything that you helped me with works, except the form still remembers the customer name entered and won't allow me to enter a different customer name until I close and re-open my form.
The button that calls this has this code behind it:

Me.Filter = "[CustomerName] = forms!frm_CustomerFilter.customers"
Me.FilterOn = True

And even when I click the button that takes away the filter (Me.Filter = "" Me.FilterOn = False) I cannot enter a new customer name.
Do you possibly know what the problem could be?

P.S. - my forms!frm_CustomerFilter.customers is just a combo box with a query behind it that shows all the names of the customers. I'm 99.9999% positive you would have known that, but just in case.
 

regikono

New member
Local time
Today, 02:50
Joined
Oct 14, 2012
Messages
3
Don't use DoCmd.ApplyFilter.

Use

Me.Filter = "[DateEnd] Between #" & DateSerial(Year(Date()),Month(Date()),1) & "# And #" & DateSerial(Year(Date()),Month(Date()),31) & "#"
Me.FilterOn = True

And to clear

Me.Filter = ""
Me.FilterOn = False



Thank you! Me.FilterOn = False was the code I was looking for. Simple way to cleanup a filtered form…
 

Users who are viewing this thread

Top Bottom