访问动态查询-更好地使用VBA构建一个条件SQL查询或多个查询? [英] Access dynamic query - Better to build one conditional SQL query or multiple queries with VBA?

问题描述

我有一个Microsoft Access 2010表单，其中包含代表某些参数的保管箱和复选框.我需要使用基于这些参数的条件来运行查询.为了提取所有数据，下拉框和复选框中也可能没有任何条件.

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. 我用WHERE子句中的IIf语句构建查询，将语句嵌套，直到我考虑了每种标准组合.我通过使用例如Forms!frmMyFrm!checkbox1或通过使用函数FormFieldValue(formName,fieldName)来引用SQL逻辑中的条件，该函数使用窗体和控件名称的输入返回控件的值(这是由于先前的问题).我将此查询设置为按下表单按钮即可运行.

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.

我设置了一个vba sub来通过按下按钮来运行.我检查条件，并根据控制标准(以与先前方法相同的方式引用)将查询SQL设置为预定的SQL字符串.这也涉及许多If...Else语句，但是比大型查询更易于阅读.

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?

推荐答案

我不相信您会发现一种方法比另一种方法更有效，至少不会明显.在大多数情况下，这只是个人喜好.

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.

我通常使用VBA并检查每个下拉列表/复选框的值，并构建SQL查询片段，然后将其放在一起.但是，使用此方法可能会遇到的问题是，如果您有大量的下拉菜单和复选框，那么代码很容易丢失".

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.

这篇关于访问动态查询-更好地使用VBA构建一个条件SQL查询或多个查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！