如何在 MS Access 2003 中创建参数化查询并使用其他查询/表单来填充参数并获取结果集 [英] How do you create a parameterized query in MS Access 2003 and use other queries/forms to fill the parameters and obtain a resultset

问题描述

我希望能够在 MS Access 2003 中创建参数化查询并将某些表单元素的值提供给该查询，然后返回相应的结果集并使用它们进行一些基本计算.我想弄清楚如何获取要由表单元素填充的查询参数.如果我必须使用 VBA，那没问题.

I'd like to be able to create a parameterized query in MS Access 2003 and feed the values of certain form elements to that query and then get the corresponding resultset back and do some basic calculations with them. I'm coming up short in figuring out how to get the parameters of the query to be populated by the form elements. If I have to use VBA, that's fine.

推荐答案

对窗体上控件的引用可以直接在 Access 查询中使用，但将它们定义为参数很重要(否则，最近版本的 Access 中的结果可能在他们曾经可靠的地方变得不可预测).

References to the controls on the form can be used directly in Access queries, though it's important to define them as parameters (otherwise, results in recent versions of Access can be unpredictable where they were once reliable).

例如，如果您想通过 MyForm 上的姓氏控件过滤查询，您可以将其用作条件:

For instance, if you want to filter a query by the LastName control on MyForm, you'd use this as your criteria:

LastName = Forms!MyForm!LastName

然后您将表单引用定义为参数.生成的 SQL 可能如下所示:

Then you'd define the form reference as a parameter. The resulting SQL might look something like this:

PARAMETERS [[Forms]!MyForm![LastName]] Text ( 255 );
SELECT tblCustomers.*
FROM tblCustomers
WHERE tblCustomers.LastName=[Forms]![MyForm]![LastName];

但是，我会问为什么您需要为此目的保存查询.你对结果做什么?在表单或报告中显示它们?如果是这样，您可以在表单/报表的记录源中执行此操作，并且参数不会影响您保存的查询，因此它可以在其他上下文中使用而不会弹出提示填写参数.

I would, however, ask why you need to have a saved query for this purpose. What are you doing with the results? Displaying them in a form or report? If so, you can do this in the Recordsource of the form/report and leave your saved query untouched by the parameters, so it can be used in other contexts without popping up the prompts to fill out the parameters.

另一方面，如果您在代码中执行某些操作，只需动态编写 SQL 并使用表单控件的字面值来构建您的 WHERE 子句.

On the other hand, if you're doing something in code, just write the SQL on the fly and use the literal value of the form control for constructing your WHERE clause.

这篇关于如何在 MS Access 2003 中创建参数化查询并使用其他查询/表单来填充参数并获取结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！