Query filter: better to use variable criteria or "applyfilter"?

[RobOtowski]

Hey Gang,



I recently had to split a database and discovered that many of my reports had issues (probably poor design, since I'm pretty new to all this still, but I kept getting the "can't open any more databases" error...). I've managed to get around many of them by creating ad hoc local tables so I got that going for me, which is nice.

That's just background information as to why I'm asking about the following. As I'm developing the front-end it occurred to me that there are multiple ways to filter queries and I'm just wonder what the difference really is.

Here's the example:
I have a form with two combo boxes, one for Term and one for Department. There is a button to open a query which will show me all the course sections from that department for that term which are not marked "correct". I click the button, it fires an embedded macro (using Macro Builder, I really don't know coding as yet) that opens the query "IncorrectListingsByDept". Under the field "CourseTerm" I have the criteria [Forms]![FormLaunch]![ComboTermSelect] and under the field "CourseDept" I have the criteria [Forms]![FormLaunch]![ComboIncorrectDept]. Under the field "SectionCorrect" I have the criteria "No". All of this works fine.

But another method is to add an "ApplyFilter" action to the existing macro with the "Where Condition" being [IncorrectListingsByDept]![CourseTerm]=[Forms]![FormLaunch]![ComboTermSelect] And [IncorrectListingsByDept]![CourseDepartment]=[Forms]![FormLaunch]![ComboIncorrectDept] Which also works fine (the "SectionCorrect" criteria stays "No" so that's not in the Where Condition line).

So my question is: Is there really a difference between the two? Or is it a six of one, 1/2 dozen of the other deal? I've run it both ways and received no errors and didn't notice a difference in how long it took to run so at least on the surface I see no difference.

Any light you can shed on this would be greatly appreciated!

[Micron]

If you base a form on a table or query and apply a filter, you will still load all of the underlying records into the form.
If you restrict the records via query criteria, you don't.
If you need to load all the underlying records into a form then allow the user to alter the records displayed by choosing a value from a combo box, you need to be able to filter out the ones that don't meet the combo box criteria. In that case, I'd apply the filter only on user demand. It wouldn't make sense to requery the form and use system resources upon each user selection when you can simply hide the unwanted records by applying a filter. I would not apply filters on form opening unless I want to load all the records anyway (to start with) but knew exactly which ones need to be shown at the start. Can't recall ever doing that, though.

[RobOtowski]

I think I'm understanding most of that. Basically if I set the criteria with the ApplyFilter action within the macro then I could, in theory, edit the query to find other records should the need arise. Whereas if I set the criteria within the query itself then that's all I'll get. (Though, with a query, I'd have to change it manually no matter what. So that's really about downstream stuff like forms and reports.) And, ultimately, if the "finished product", be it a form or a report, may need alternate filtering (like switching from fall 16 to summer 16) then it's best to apply the filtering as far down stream as possible, making more records available to the form rather than requerying the underlying form. Is that what you're saying?


For the most part, any forms or reports that I use are pretty specific (e.g. I never want to view the records for every fall term, only for fall 2016) so I have forms and reports based on queries with the criteria built in (e.g. under the field "CourseDept" in the criteria row is [Forms]![FormLaunch]![ComboIncorrectDept] within the query itself, so every time the query loads it will look at that combo box for what to do). Would it perhaps be better to launch the form and put a combo box there to select the term/department/what-have-you? I'm not sure that I have any forms where the filtering is done within the form, but rather the underlying queries.


But what I'm asking about above is strictly for opening a specific query where I'm always going to need to clarify, at the very least, the specific term. Would it make more sense/be more practical to put the criteria "check that combo box" (not the actual wording, I know) into the query's criteria row itself, or to put that in an ApplyFilter action within the macro that will open the query. Or, since there's no reports or forms being built from this particular query, does it not matter, just go with whichever makes me happier?


As always, Micron, thanks for your input!

[Micron]

I think I'm understanding most of that. Basically if I set the criteria with the ApplyFilter action within the macro then I could, in theory, edit the query to find other records should the need arise.

Not really. You would not edit the query unless it no longer suits your purpose; you'd alter the filter. Lets say the records are a bag of marbles (the bag is the table or query). If I pass you the entire bag (the whole table or a query on it that has no criteria) and you pick out only the blue ones, you just filtered the selection. You can put them back in the bag and then choose only the red ones (apply a new filter). If you want me to pass you only the blue ones in a bag, you have set the criteria up front and cannot apply a filter to change the colour selection. You could apply a size filter though.

For the most part, any forms or reports that I use are pretty specific (e.g. I never want to view the records for every fall term, only for fall 2016) so I have forms and reports based on queries with the criteria built in (e.g. under the field "CourseDept" in the criteria row is [Forms]![FormLaunch]![ComboIncorrectDept] within the query itself

Nothing wrong with that if it works. Why enforce a user to make a selection that you can default to the current year or time frame if that's all you'll ever need. But a combo box requires user interaction, so a text box might be the preferred control.

Whereas if I set the criteria within the query itself then that's all I'll get. (Though, with a query, I'd have to change it manually no matter what.

Yes to the first part. No to the second if it's looking at form controls for criteria. The exception would be if you need to add/remove a field as criteria.

So that's really about downstream stuff like forms and reports.) And, ultimately, if the "finished product", be it a form or a report, may need alternate filtering (like switching from fall 16 to summer 16) then it's best to apply the filtering as far down stream as possible, making more records available to the form rather than requerying the underlying form. Is that what you're saying?

Only if it makes sense to provide more records to the first form than what is required in the following form or report. If for example, I open a form with all records loaded then intend to open a report based on a user selection from one of those records, I'd open the report using the selected value as criteria in the query underlying the report. Why open a report loaded with all records then filter it? The exception would be if the report query is not referencing controls on a form. In that case, you'd need to apply a filter. Confused enough? I should stop

But what I'm asking about above is strictly for opening a specific query where I'm always going to need to clarify, at the very least, the specific term. Would it make more sense/be more practical to put the criteria "check that combo box" (not the actual wording, I know) into the query's criteria row itself,

As long as you're not trying to run the query on form open since the control is not available. You'd be better doing it in the Load event and ensuring the query returns all results if the combo has no value. Then in the after update event, you'd either apply a filter or requery if you're staying with that form. If you're loading a report or another form, go with what's already been said. I think you are correct in doing which you find easier if there would not be a big hit on system resources or slowing down the form. If you're filtering a form with 200,000 records behind it and you don't need to, that makes no sense at all.

[RobOtowski]

Thanks, Micron.

On the first point, I think I just did a poor job explaining it because your analogy is very close to the one I was using in my head.

But a combo box requires user interaction, so a text box might be the preferred control.

I'm not sure I understand how using a text box is preferred. And in this case I should point out that "fall 2016" is actually "201680", which only about 1/2 of the users would know, so if I have them typing it in instead of selecting from a combo box they'd be constantly asking me "what's the freekin' code again?"

Why open a report loaded with all records then filter it?

Yeah, I was thinking about how little sense it made to run a report and then refine it, rather than the other way around. I could see the value for a form, though.

Overall I think I'm getting it. It sounds like for the exact instance of opening a single stand-alone query, just to see what records are in it, not to edit, not to print, never going to build a form or a report based on it, I can go either way with the filtering. If there is a downstream use, such as a form, then filter it as late as possible in order to give me and other users the ability to modify the results without starting from zero.

I believe that this will help me clean up the database and maybe make the front end less hinky. I really appreciate the help!

[Micron]

I'm not sure I understand how using a text box is preferred.

After re-reading your posts, I see that I confused a comment made elsewhere about someone not wanting to have user interaction (they were using a combo box) with your post. I was dealing with more than one issue at a time. Sorry for any confusion that might have caused.

[RobOtowski]

Sorry for any confusion that might have caused.

I lost so much sleep last night over this...