If you close the report without explicitly clearing the filters, Access remembers them and you can click Toggle Filter again to reapply them next time you open the report. This works even if you close and reopen the database. However, if you click Home > Advanced > Clear All Filters, Access clears the filters completely and you’ll need to start from scratch next time around.
Interesting to know how Access saves the filter you wrote.
Searching is the #1 task that a user has to do. I will say this again:Searching is the #1 task that a user has to do.
Keep reading. Maybe this has to do with server filtering. I don't know. But it seems interesting so ...
In the example(s), Mypass is a SQL pass-through query you created. You simply create one working PT query, and then you can (usually) then use that one PT query anytime you want raw T-SQL or to run a store procedure 100% server side. So we are in effect overwriting and re-using that one PT query anytime we want raw SQL to run server side.
This tells how Access has changed over the years and how different it is from the version I worked with ages ago.
Now for running and executing store procedures, or say a sql update to “many” rows, then you most certainly want to use a pass through query, since processing of records does occur local client side.
So, it's important to research and learn about pass-through queries.
And AGAIN a SIMPLE USE of the “where” clause will result in sql server view ONLY sending down records that meet the criteria you set via the Access built in “where” clause.
So, building the where clause when opening forms/report is one of the best ways to go.
So placing forms! Expressions inside of a query always been a bad idea since then the query is now “married” to some silly form.
Wouldn't myself imagined this. But it's very the very truth. The query becomes attached to the form. You can not use it anywhere else!!! And this is the way I was doing this ...
You can change the SQL string in the pass-through QueryDef and specify the Form field:
So, this is a way to execute that query on the sql server side. Would it be difficult to implement?
Second, would be to pass a SQL string from Access to SQL Server (pass-through query) and have it execute on the SQL Server side.
Ok, so we need to do some research about pass-trhrough queries. Do they execute on the server?
You can make these changes 100% inside of Access, and not have to really use anything SQL server side to achieve this goal.
This is just wonderful. Just need to work on the Access (client) side. No need to affect the server side (always more difficult to work with)
WhereCondition Optional Variant A string expression that's a valid SQL WHERE clause without the word WHERE.
According to what I read, this Where clause goes to the server and applies there. So, we just have to do what we do in js: open a filter form first, build a filter from there and then open the form with that filter (with no Where word).
Doing that, it's sql server who picks up that filter and applies it server side!!! Just what we need ...
Essentially, sorting and filtering are tools that let you organize your data. When you sort data, you are putting it in order. Filtering data lets you hide unimportant data and focus only on the data you're interested in.
Some inputs about sorting and filtering in an Access Database.