Access dynamic query - Better to build one conditi

Question

I have a Microsoft Access 2010 form with dropboxes and a checkbox which represent certain parameters. I need to run a query with conditions based on these parameters. It should also be a possibility for no criteria from the dropdown boxes and checkbox in order to pull all data.

I have two working ways of implementing this:

1. I build a query with IIf statements in the WHERE clause, nesting statements until I have accounted for every combination of criteria. I reference the criteria in the SQL logic by using Forms!frmMyFrm!checkbox1 for example or by using a function FormFieldValue(formName,fieldName) which returns the value of a control with the input of the form and control name (This is because of previous issues). I set this query to run with the press of the form's button.

2. I set a vba sub to run with the press of the button. I check the conditions and set the query SQL to a predetermined SQL string based on the control criteria (referenced in the same way as the previous method). This also involves many If...Else statements, but is a little easier to read than a giant query.

What is the preferred method? Which is more efficient?

Answer 1

I don't believe you would find one way is more efficient over the other, at least not noticeably. For the most part it is simply personal preference.

I generally use VBA and check the value of each dropdown/checkbox and build pieces of the SQL query then put together at the end. The issue that you may run into with this method though is that if you have a large number of dropdowns and checkboxes the code is easy to get "lost" in.

If time to run is very key though you could always use some of the tips How do you test running time of VBA code? to see which way is faster.

Answer 2

After a lot of experimentation, and a bit of new information indicating having a pre-built query is faster than having SQL compiled in VBA, the most efficient and clear solution in the context of Microsoft Access is to build and save a number of dependent queries beforehand.

Essentially, build a string of queries each with an IIf dependent on a different criterium. Then you only need to run the final query. The only case where you would have to incorporate a VBA If...Else is if you need to query something more complicated than SELECT...WHERE(IIf(...)).

This has a few advantages:

1. The SQL is already compiled in the saved query, speeding things up.
2. No more getting lost in code:
   • There is no giant, nearly-impossible-to-edit query with way too many IIfs.
   • The minimal VBA code is even easier to follow.
3. At least for me, who's not an expert in SQL, it's convenient that I can often use the MS Access visual query builder for each part.