1

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?

ZX9
  • 898
  • 2
  • 16
  • 34
  • I prefer the most efficient method. I determine that by trying both. – Dan Bracuk Jan 28 '15 at 19:27
  • @DanBracuk My current solution involves a combination of both methods, actually, which works best for me for various reasons. I was attempting to see if there's any more technical reason for one over the other, as well as providing a place for others who may have this question (but have not necessarily put as much effort into researching it) to go. – ZX9 Jan 28 '15 at 20:32
  • 1
    I wonder if JET/ACE goes to check the form each time a tuple is tested against the WHERE [field] = FormValue... I would stick to the VBA on this one and comment liberally throughout your code. – dennythecoder Jan 29 '15 at 01:36
  • I had thought of that originally too, but you would think that the time for a query within access to get something from a form, wouldn't be much longer than for VBA to go and get something from a form. Hard to say really perhaps ZX9 can come back in the future and let us know once they run into larger queries. . – Newd Jan 29 '15 at 18:51

2 Answers2

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.

Community
  • 1
  • 1
Newd
  • 2,174
  • 2
  • 17
  • 31
  • My initial testing indicates the difference is minimal, as you predicted. However, I will (eventually) continue testing as some of my queries reference a substantial amount of information and the time required may be significant. – ZX9 Jan 29 '15 at 17:38
0

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.
ZX9
  • 898
  • 2
  • 16
  • 34