blocks|key|6014713|text|我觉得你做不到。您是如何运行查询的？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6014714|我要说的是，如果您需要一个具有那么多开放变量的查询，请将其放在vba模块或类中，并调用它，让它每次都构建字符串。|6014715|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|6014765|text|我不确定这是否有帮助，因为我怀疑您希望使用已保存的查询而不是在VBA中执行此操作；但是，您可以做的最简单的事情是在VBA中逐行构建查询，然后从该查询创建记录集。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6014766|一种非常老套的方法是动态重写保存的查询，然后访问该查询；但是，如果有多个人使用同一个DB，则可能会遇到冲突，并且会使下一位开发人员感到困惑。|6014767|您也可以通过编程将默认值传递给查询(正如您在上一个问题中所讨论的)。|6014768|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

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.

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

blocks|key|6014806|text|您可以通过将*作为不希望在当前筛选器中使用的字段的参数来返回非空值。在Access+2003+(以及可能的更早和更高版本)中，如果您使用like+[paramName]作为数字、文本、日期或布尔域的条件，星号将显示所有记录(符合您指定的其他条件)。如果您还想返回空值，那么可以使用like+[paramName]+or+Is+Null作为条件，这样它就可以返回所有记录。(如果您是在代码中构建查询，则效果最好。如果您正在使用现有的查询，并且您不希望在有要筛选的值时返回空值，那么这将不起作用。)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|6014807|如果要过滤备注字段，则必须尝试另一种方法。|6014808|entityMap^0|1W|G|3W|R|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

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 <code>like [paramName]</code> 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 <code>like [paramName] or Is Null</code> as 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.)

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

blocks|key|2486800|text|如果你这样构造你的查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2486801|PARAMETERS+ParamA+Text+(+255+);
SELECT+t.id,+t.topic_id
FROM+SomeTable+t
WHERE+t.id+Like+IIf(IsNull([ParamA]),"*",[ParamA])|code-block|syntax|javascript|2486802|如果未填写该参数，则所有记录将被选中。|2486803|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If you construct your query like so:

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

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

blocks|key|4597797|text|回到你上一个问题中的前一个例子。您的参数化查询是如下所示的字符串：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4597798|qr+=+"Select+Tbl_Country.*+From+Tbl_Country+WHERE+id_Country+=+[fid_country]"|code-block|syntax|javascript|4597799|根据fid_Country的性质(数字、文本、guid、日期等)，您必须将其替换为一个搞笑器值和特定的分隔字符：|4597800|qr+=+replace(qr,"[fid_country]","""*""")|4597801|为了完全允许通配符，您的原始查询也可以是：|4597802|qr+=+"Select+Tbl_Country.*+From+Tbl_Country+_
++++++WHERE+id_Country+LIKE+[fid_country]"|4597803|然后，您可以获得fid_Country的通配符值，例如|4597804|qr+=+replace(qr,"[fid_country]","G*")|4597805|完成此操作后，即可使用该字符串打开记录集|4597806|set+rs+=+currentDb.openRecordset(qr)|4597807|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

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

<pre><code>qr = "Select Tbl_Country.* From Tbl_Country WHERE id_Country = [fid_country]"
</code></pre>

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:

<pre><code>qr = replace(qr,"[fid_country]","""*""")
</code></pre>

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

<pre><code>qr = "Select Tbl_Country.* From Tbl_Country _
 WHERE id_Country LIKE [fid_country]"
</code></pre>

You can then get wild card values for fid_Country such as

<pre><code>qr = replace(qr,"[fid_country]","G*")
</code></pre>

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

<pre><code>set rs = currentDb.openRecordset(qr)
</code></pre>

blocks|key|4597854|text|注带有LIKE关键字的ANSI通配符仅在*+-89查询模式下具有所需的效果。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4597855|许多人错误地认为Access/Jet中的通配符总是*。事实并非如此。Jet有两个通配符:+ANSI-92查询模式下的%25和ANSI-89查询模式下的*。|4597856|ADO始终为ANSI-92，DAO始终为ANSI-89，但访问接口可以是二者之一。|4597857|当在数据库对象中使用LIKE关键字(例如，将持久保存在mdb文件中的内容)时，您应该考虑:如果有人使用查询模式而不是我自己通常使用的查询模式来使用此数据库，会发生什么？假设您想要将文本字段限制为仅限数字字符，并且您的验证规则编写如下：|4597858|NOT+LIKE+"*[!0-9]*"|code-block|syntax|javascript|4597859|如果有人无意中(或以其他方式)通过ADO连接到您的.mdb，那么上面的验证规则将允许他们添加包含非数字字符的数据，您的数据完整性将会失效。不太好。|4597860|最好是始终为两种ANSI查询模式编写代码。也许这最好通过对两种模式进行显式编码来实现，例如|4597861|NOT+LIKE+"*[!0-9]*"+AND+NOT+LIKE+"%25[!0-9]%25"|4597862|但是随着Jet+SQL+DML/DDL越来越复杂，这可能很难简明扼要地实现。这就是为什么我建议使用ALIKE关键字，它使用ANSI-92查询模式通配符，而不考虑查询模式。|4597863|NOT+ALIKE+"%25[!0-9]%25"|4597864|类似的笔记是没有记录的(我想这就是为什么我的原始帖子被标记下来的原因)。我已经在Jet3.51+(Access97)、Jet4.0+(Access2000到2003)和ACE+(Access2007)上测试过了，它工作得很好。我之前已经在新闻组中发布了这篇文章，并获得了Access+MVP的批准。通常情况下，我自己会避开没有文档记录的功能，但在这种情况下例外，因为Jet已经被弃用了近十年，而让它继续存在的访问团队似乎对对引擎进行深层次的更改(或错误修复！)不感兴趣，这会使Jet引擎成为一个非常稳定的产品。|4597865|有关Jet的ANSI查询模式的详细信息，请参阅About+ANSI+SQL+query+mode。|4597866|entityMap|0|LINK|mutability|MUTABLE|url|http://web.archive.org/web/20140614093902/http://office.microsoft.com/en-gb/access-help/about-ansi-sql-query-mode-mdb-HP003070483.aspx^0|3|4|K|1|0|0|0|0|0|0|0|0|0|0|0|N|P|0|0^^$0|@$1|2|3|4|5|6|7|1C|8|@$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1H|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|1I|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|1J|8|@]|D|@]|E|$]]|$1|L|3|M|5|N|7|1K|8|@]|D|@]|E|$O|P]]|$1|Q|3|R|5|6|7|1L|8|@]|D|@]|E|$]]|$1|S|3|T|5|6|7|1M|8|@]|D|@]|E|$]]|$1|U|3|V|5|N|7|1N|8|@]|D|@]|E|$O|P]]|$1|W|3|X|5|6|7|1O|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|N|7|1P|8|@]|D|@]|E|$O|P]]|$1|10|3|11|5|6|7|1Q|8|@]|D|@]|E|$]]|$1|12|3|13|5|6|7|1R|8|@]|D|@$9|1S|A|1T|1|1U]]|E|$]]|$1|14|3|-4|5|6|7|1V|8|@]|D|@]|E|$]]]|15|$16|$5|17|18|19|E|$1A|1B]]]]

Note the <code>*</code> wildcard with the <code>LIKE</code> keyword will only have the desired effect in ANSI-89 Query Mode.

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. 

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

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:

<pre><code>NOT LIKE "*[!0-9]*"
</code></pre>

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.

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

<pre><code>NOT LIKE "*[!0-9]*" AND NOT LIKE "%[!0-9]%"
</code></pre>

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. 

<pre><code>NOT ALIKE "%[!0-9]%"
</code></pre>

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. 

For more details on Jet's ANSI Query modes, see <a href="http://web.archive.org/web/20140614093902/http://office.microsoft.com/en-gb/access-help/about-ansi-sql-query-mode-mdb-HP003070483.aspx" rel="nofollow noreferrer">About ANSI SQL query mode</a>.

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?

How can I make MS Access Query Parameters Optional?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个查询，我想在不同的时间以不同的方式进行过滤。我现在已经通过在相关查询字段的criteria字段中放置参数来实现这一点，但是在许多情况下，我不想对给定的字段进行过滤，而只想对其他字段进行过滤。有没有什么方法可以将某种类型的通配符传递给criteria参数，这样我就可以绕过对查询的特定调用的过滤？

问如何使MS Access查询参数成为可选的？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使MS Access查询参数成为可选的？EN