如何在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上的LastName控件过滤查询，可以将其用作条件:

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屋！