Acces table filter with parameter

Question

Acc2010 "APPLY FILTER/SORT" does not work with parameter (f.ex [Enter name]) in table view.  If I enter [Enter name] in "ADVANCE FILTER/SORT" criteria ("FILTER FOR" has been removed from right-click) and toggle "FILTER" I am prompted for "Enter name".  If I press "APPLY FILTER/SORT" I am not prompted for an entry - the earlier entry is applied without a prompt!  In ACC2003 I can enter a parameter in the right-click "FILTER FOR" field or "ADVANCE FILTER/SORT" criteria and press "APPLY FILTER/SORT" all day for new filter values.  I can't think of any use for "APPLY FILTER/SORT" other than supporting a dynamic component that allows for changed values to filtration.  Otherwise the toggle filter on/off does the same.

Answer 1

I figured it out - after you put [parameter(s)] in a filter (enter value as a parameter/advance filter) you must press REFRESH ALL to re-filter the data on a new value in parameter prompt - in 2003 and before I pressed "APPLY FILTER" for the same.

Answer 2

I agree with your statement when it refers to business data.  Entry and use needs to be safeguarded when there are multiple users of the data.

Access is a also a very powerful analytical tool.  I can keep or import data into tables, copy data back and forth between access and excel (each has its strong points).  Sure, there are ways to accomplish what I'm doing in other ways in access but they are either cumbersome and require additional repetitive steps, or (pseudo) programming.  I don't sit down and create a query, or macros for every ad hoc filtered view only to delete it after use.

Answer 3

Thanks for your reply.

When I enter a parameter [...] in "advance filter" criteria it is a placeholder for a variable, and when executed prompts for the value.  "Apply filter" should prompt for a new value every time - all versions of access up to 2007 did that.  In 2007 and 2010 "apply filter" applies the filter value again without a prompting for a new value, which is wrong!

What is the purpose of a parameter in a query if I have to "clear all filters" after every time I use it and go and enter the parameter again - I might as well enter the value as a constant instead of a parameter.

Answer 4

Bottom line is users should not interact directly with tables (as advised in another question).

Answer 5

If I press "APPLY FILTER/SORT" I am not prompted for an entry - the earlier entry is applied without a prompt!

I would consider that the correct behavior based on the Microsoft documentation regarding filters. Applying a filter is intended to give you a filtered view of the data. I would expect to see that filtered view until I removed the filter, and I would only expect to receive a prompt if I have removed the filter I set in the Advanced Filter/Sort process.

Or perhaps I've misunderstood your question?

I referred to this article regarding filters:

https://support.office.com/en-us/article/Apply-a-filter-to-view-select-records-in-an-Access-database-2a493ded-e544-4144-9103-b9b1d1865147