Ms Access 简明教程
MS Access - Wildcards
通配符是特殊字符，可以在文本值中代表未知字符，并且非常适合于查找具有相似但不相同数据的多个项目。通配符还可以帮助根据特定模式匹配获取数据库。
Wildcards are special characters that can stand in for unknown characters in a text value and are handy for locating multiple items with similar, but not identical data. Wildcards can also help with getting databased on a specified pattern match.
Access 支持两组通配符，因为它支持两种结构化查询语言标准。
Access supports two sets of wildcard characters because it supports two standards for Structured Query Language.
-
ANSI-89
-
ANSI-92
一般来说，在对 Access 数据库（例如 .mdb 和 .accdb 文件）运行 queries 和 find-and-replace 操作时，您使用 ANSI-89 通配符。
As a rule, you use the ANSI-89 wildcards when you run queries and find-and-replace operations against Access databases such as .mdb and .accdb files.
在对 Access 项目（连接到 Microsoft SQL Server 数据库的 Access 文件）运行查询时，您使用 ANSI-92 通配符。Access 项目使用 ANSI-92 标准，因为 SQL Server 使用该标准。
You use the ANSI-92 wildcards when you run queries against Access projects — Access files connected to Microsoft SQL Server databases. Access projects use the ANSI-92 standard because SQL Server uses that standard.
ANSI-89 Wildcard Characters
下表列出了 ANSI-89 支持的字符 -
The following table lists out characters supported by ANSI-89 −
Character |
Description |
Example |
* |
Matches any number of characters. You can use the asterisk (*) anywhere in a character string. |
wh* finds what, white, and why, but not awhile or watch. |
? |
Matches any single alphabetic character. |
B?ll finds ball, bell, and bill. |
[ ] |
Matches any single character within the brackets. |
B[ae]ll finds ball and bell, but not bill. |
! |
Matches any character not in the brackets. |
b[!ae]ll finds bill and bull, but not ball or bell. |
- |
Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A). |
b[a-c]d finds bad, bbd, and bcd. |
# |
Matches any single numeric character. |
1#3S finds 103, 113, and 123. |
ANSI-92 wildcard characters
下表列出了 ANSI-92 支持的字符 -
The following table lists out characters supported by ANSI-92 −
Character |
Description |
Example |
% |
Matches any number of characters. It can be used as the first or last character in the character string. |
wh% finds what, white, and why, but not awhile or watch. |
_ |
Matches any single alphabetic character. |
B_ll finds ball, bell, and bill. |
[ ] |
Matches any single character within the brackets. |
B[ae]ll finds ball and bell, but not bill. |
^ |
Matches any character not in the brackets. |
b[^ae]ll finds bill and bull, but not ball or bell. |
- |
Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A). |
b[a-c]d finds bad, bbd, and bcd. |
Example
现在，让我们通过打开查询设计来了解使用这些通配符的简单示例。
Let us now look at a simple example of using these wildcard characters by opening the query design.
添加高亮的表并关闭 Show Table 对话框。
Add the highlighted tables and close the Show Table dialog box.
在查询网格中添加想要作为查询结果查看的字段。
Add the fields in the query grid which you want to see as a query result.
运行查询。
Run your query.
让我们再次转到 query design 并添加项目名称提示符。
Let us again go to the query design and add prompt for project name.
现在运行查询，让我们假设你不知道确切的项目名称，但你知道项目名称包含单词“potion”。单击 Ok 。
Now run your query and let us assume that you don’t know the exact project name, but you know that the project name contains the words “potion”. Click Ok.
上述步骤不会生成任何结果。这是因为 Access 正在 project name field 中寻找完全匹配。它正在寻找名称中包含单词 potion 的项目。
The above step does not generate any result. That is because Access is looking for an exact match in the project name field. It is looking for that project which has the word potion in its name.
如果你想要让用户输入通配符来替换未知字符，那么你需要调整条件并包括运算符 like 。
If you want it so that the users can enter wildcards to replace unknown characters, then you need to adjust the criteria and include the operator like.
运行查询时，用户可以使用通配符替换任意数量的字符。
When you run the query, users can use wildcards to replace any number of characters.
让我们假设你知道单词 potion 出现在标题中的某处，但你无法确定具体位置。
Let us assume that you know that the word potion appears somewhere in the title but you are not exactly sure where.
你可以在 potion 前添加一个 * 来替换任意数量的字符，然后在后面再添加一个 。单击 *Ok 。
You can add an * to replace any number of characters before the word potion and then add another * after the. Click Ok.
用户了解他们可以输入哪些通配符总是有益的。但有些用户可能不知道他们可以和不可以输入哪些通配符。在这种情况下，你可以自己输入通配符。
It is always good for the users to know the wildcards they can enter. But there are users who may not know the wildcards they can and cannot enter. In that case, you can enter the wildcards yourself.
在这种情况下，我们可以在 Like 运算符和我们的参数提示符之间添加这些通配符，现在我们必须用一种非常明确的方式来编写它。在带引号的 like 词后，输入我们正在使用的通配符。在这种情况下，我们使用了“ ” to replace any number of characters. We will now add this to the parameter. To do so, we need ampersand *(&) 符号和一个空格。现在，我们将重复此操作，并添加另一个和号 (&)，因为我们正在将该通配符连接到用户为该项目名称输入的任何条件，然后在引号中输入“*”。
In this situation, in-between the Like operator and our parameter prompt, we can add those wildcards and now there’s a very specific way we have to write this. After the word like in quotation marks, enter the wildcard that we are using. In this case, we have used “” to replace any number of characters. We will now add this to the parameter. To do so, we need ampersand *(&) symbol and a space. We will now repeat this step and add another ampersand (&) because we’re joining that wildcard to whatever criteria is entered in for that project name by the user and then “*” in quotes.
我们现在重新运行查询。在提示中输入单词 potion ，不使用通配符。
Let us now run our query again. Enter the word potion in the prompt without any wildcards.
该查询现在将在此处输入内容的任一侧跟踪这些通配符。我们需要直接输入药剂并按 Enter。
The query will now track those wildcards on either side of whatever is entered here. We need to simply type potion and press enter.
我们现在得到了我们想要的结果，无论用户输入什么，结果都将相同。
We now get the results we were looking for and the results will be the same regardless of what the users enter.
让我们假设我们要查找标题中包含单词“the”的每个项目。然后，您只需要输入该单词和 Enter 或单击 Ok 。
Let us say we want to find every project with the word “the” in the title. Then, you need to just type the word and Enter or click Ok.
使用此查询，可以更容易地搜索名称中包含单词“the”的项目。第 2 级结果可能还包含单词“the”是其中一部分的单词“mother”的项目名称。
With this query, it becomes easier to search for projects with the word “the” in their names. The 2nd level of results may also include the project names with the word “mother” where “the” is part of the word.