SQL 如何使 MS Access 查询参数可选？

How can I make MS Access Query Parameters Optional?

提问by Tim Visher

I have a query that I would like to filter in different ways at different times. The way I have done this right now by placing parameters in the criteria field of the relevant query fields, however there are many cases in which I do not want to filter on a given field but only on the other fields. Is there any way in which a wildcard of some sort can be passed to the criteria parameter so that I can bypass the filtering for that particular call of the query?

我有一个查询，我想在不同的时间以不同的方式进行过滤。我现在通过在相关查询字段的标准字段中放置参数来完成此操作的方式，但是在许多情况下，我不想过滤给定字段而只想过滤其他字段。有什么方法可以将某种通配符传递给标准参数，以便我可以绕过针对特定查询调用的过滤？

回答by Fionnuala

If you construct your query like so:

如果您像这样构造查询：

PARAMETERS ParamA Text ( 255 );
SELECT t.id, t.topic_id
FROM SomeTable t
WHERE t.id Like IIf(IsNull([ParamA]),"*",[ParamA])

All records will be selected if the parameter is not filled in.

如果未填写该参数，则将选择所有记录。

回答by onedaywhen

Note the *wildcard with the LIKEkeyword will only have the desired effect in ANSI-89 Query Mode.

请注意，*带有LIKE关键字的通配符仅在 ANSI-89 查询模式中具有所需的效果。

Many people mistakenly assume the wildcard character in Access/Jet is always *. Not so. Jet has two wildcards: % in ANSI-92 Query Mode and * in ANSI-89 Query Mode.

许多人错误地认为 Access/Jet 中的通配符总是 *。不是这样。Jet 有两个通配符：ANSI-92 查询模式中的 % 和 ANSI-89 查询模式中的 *。

ADO is always ANSI-92 and DAO is always ANSI-89 but the Access interface can be either.

ADO 始终是 ANSI-92，DAO 始终是 ANSI-89，但 Access 接口可以是两者之一。

When using the LIKE keyword in a database object (i.e. something that will be persisted in the mdb file), you should to think to yourself: what would happen if someone used this database using a Query Mode other than the one I usually use myself? Say you wanted to restrict a text field to numeric characters only and you'd written your Validation Rule like this:

当在数据库对象中使用 LIKE 关键字时（即一些将保留在 mdb 文件中的内容），您应该自己思考：如果有人使用我通常使用的查询模式以外的查询模式使用该数据库会发生什么？假设您想将文本字段限制为仅限数字字符，并且您编写了如下验证规则：

NOT LIKE "*[!0-9]*"

If someone unwittingly (or otherwise) connected to your .mdb via ADO then the validation rule above would allow them to add data with non-numeric characters and your data integrity would be shot. Not good.

如果有人无意中（或以其他方式）通过 ADO 连接到您的 .mdb，那么上面的验证规则将允许他们添加具有非数字字符的数据，并且您的数据完整性将被破坏。不好。

Better IMO to always code for both ANSI Query Modes. Perhaps this is best achieved by explicitly coding for both Modes e.g.

更好的 IMO 始终为两种 ANSI 查询模式编码。也许这最好通过对两种模式进行显式编码来实现，例如

NOT LIKE "*[!0-9]*" AND NOT LIKE "%[!0-9]%"

But with more involved Jet SQL DML/DDL, this can become very hard to achieve concisely. That is why I recommend using the ALIKE keyword, which uses the ANSI-92 Query Mode wildcard character regardless of Query Mode e.g.

但是随着更多涉及 Jet SQL DML/DDL，这可能变得很难简洁地实现。这就是为什么我建议使用 ALIKE 关键字，它使用 ANSI-92 查询模式通配符，而不管查询模式如何，例如

NOT ALIKE "%[!0-9]%"

Note ALIKE is undocumented (and I assume this is why my original post got marked down). I've tested this in Jet 3.51 (Access97), Jet 4.0 (Access2000 to 2003) and ACE (Access2007) and it works fine. I've previously posted this in the newsgroups and had the approval of Access MVPs. Normally I would steer clear of undocumented features myself but make an exception in this case because Jet has been deprecated for nearly a decade and the Access team who keep it alive don't seem interested in making deep changes to the engines (or bug fixes!), which has the effect of making the Jet engine a very stable product.

注意 ALIKE 没有记录（我认为这就是为什么我的原始帖子被标记了）。我已经在 J​​et 3.51 (Access97)、Jet 4.0 (Access2000 到 2003) 和 ACE (Access2007) 中测试过这个，它工作正常。我之前已在新闻组中发布过此内容，并获得了 Access MVP 的批准。通常我会自己避开未记录的功能，但在这种情况下例外，因为 Jet 已被弃用近十年，并且保持它活跃的 Access 团队似乎对对引擎进行深入更改（或错误修复！ )，这具有使喷气发动机成为非常稳定的产品的效果。

For more details on Jet's ANSI Query modes, see About ANSI SQL query mode.

有关 Jet 的 ANSI 查询模式的更多详细信息，请参阅关于 ANSI SQL 查询模式。

回答by Philippe Grondier

Back to my previous exampe in your previous question. Your parameterized query is a string looking like that:

回到我在你上一个问题中的上一个例子。您的参数化查询是一个如下所示的字符串：

qr = "Select Tbl_Country.* From Tbl_Country WHERE id_Country = [fid_country]"

depending on the nature of fid_Country (number, text, guid, date, etc), you'll have to replace it with a joker value and specific delimitation characters:

根据 fid_Country 的性质（数字、文本、guid、日期等），您必须将其替换为小丑值和特定的分隔字符：

qr = replace(qr,"[fid_country]","""*""")

In order to fully allow wild cards, your original query could also be:

为了完全允许通配符，您的原始查询也可以是：

qr = "Select Tbl_Country.* From Tbl_Country _
      WHERE id_Country LIKE [fid_country]"

You can then get wild card values for fid_Country such as

然后，您可以获得 fid_Country 的通配符值，例如

qr = replace(qr,"[fid_country]","G*")

Once you're done with that, you can use the string to open a recordset

完成后，您可以使用字符串打开记录集

set rs = currentDb.openRecordset(qr)

回答by theo

I don't think you can. How are you running the query?

我不认为你可以。你如何运行查询？

I'd say if you need a query that has that many open variables, put it in a vba module or class, and call it, letting it build the string every time.

我会说如果你需要一个有那么多开放变量的查询，把它放在一个 vba 模块或类中，然后调用它，让它每次都构建字符串。

回答by BIBD

I'm not sure this helps, because I suspect you want to do this with a saved query rather than in VBA; however, the easiest thing you can do is build up a query line by line in VBA, and then creating a recordset from it.

我不确定这有帮助，因为我怀疑您想使用保存的查询而不是在 VBA 中执行此操作；但是，您可以做的最简单的事情是在 VBA 中逐行构建查询，然后从中创建记录集。

A quite hackish way would be to re-write the saved query on the fly and then access that; however, if you have multiple people using the same DB you might run into conflicts, and you'll confuse the next developer down the line.

一种非常hackish 的方法是即时重写保存的查询，然后访问它；但是，如果您有多个人使用同一个数据库，您可能会遇到冲突，并且会使下一个开发人员感到困惑。

You could also programatically pass default value to the query (as discussed in you r previous question)

您还可以以编程方式将默认值传递给查询（如您在上一个问题中所述）

回答by Dave DuPlantis

Well, you can return non-null values by passing * as the parameter for fields you don't wish to use in the current filter. In Access 2003 (and possibly earlier and later versions), if you are using like [paramName]as your criterion for a numeric, Text, Date, or Boolean field, an asterisk will display all records (that match the other criteria you specify). If you want to return null values as well, then you can use like [paramName] or Is Nullas the criterion so that it returns all records. (This works best if you are building the query in code. If you are using an existing query, and you don't want to return null values when you do have a value for filtering, this won't work.)

好吧，您可以通过将 * 作为您不希望在当前过滤器中使用的字段的参数传递来返回非空值。在 Access 2003（可能还有更早版本和更高版本）中，如果您使用like [paramName]数字、文本、日期或布尔字段作为标准，星号将显示所有记录（与您指定的其他标准匹配）。如果您还想返回空值，则可以将其like [paramName] or Is Null用作条件，以便它返回所有记录。（如果您在代码中构建查询，这最有效。如果您使用现有查询，并且当您有过滤值时不想返回空值，这将不起作用。）

If you're filtering a Memo field, you'll have to try another approach.

如果您要过滤备注字段，则必须尝试另一种方法。