查询 SQL 中的感叹号

Exclamation Marks in a Query SQL

提问by ChickSentMeHighE

I'm reading over this query, and I came upon a line where I don't understand heres the line

我正在阅读此查询，但遇到了一行我不明白的行

[FETT List]![FETT Search]

1. FETT List is a table
2. FETT Search is a column in FETT List

1. FETT 列表是一个表
2. FETT 搜索是 FETT 列表中的一个列

Can someone explain what the exclamation mark means?

谁能解释一下感叹号是什么意思？

Thanks

谢谢

采纳答案by Thyamine

Generally you see this in MS Access code (for the exclamation mark, a period for SQL server). You can refer to a column by table.column or if you give the table an alias, then by alias.column. You might do this if you want to be specific when using joins, or you may have to do it when two (or more) tables in a query/join have the same column name in each table.

通常，您会在 MS Access 代码中看到这一点（感叹号，SQL 服务器的句点）。您可以通过 table.column 引用列，或者如果您为表指定别名，则通过 alias.column。如果您想在使用连接时具体化，您可以这样做，或者当查询/连接中的两个（或多个）表在每个表中具有相同的列名时，您可能必须这样做。

回答by David-W-Fenton

Well, you learn something new every day!

好吧，你每天都会学到新东西！

I had originally planned to explain that if you'd said the reference was [Forms]![FETT List]![FETT Search], then it would be easy to explain, as a reference to the [FETT Search] control on the [FETT List] form. But without a parent collection (either Reports of Forms), it doesn't look like a valid reference in any context within a SQL statement.

我原本打算解释一下，如果你说引用是 [Forms]![FETT List]![FETT Search]，那么解释起来很容易，作为对 [FETT Search] 控件的引用FETT 列表] 形式。但是如果没有父集合（表单报告），它在 SQL 语句中的任何上下文中看起来都不是有效的引用。

But then I thought to test it, and discovered (to my surprise) that this SQL statement is treated as valid in an Access form:

但后来我想测试它，并发现（令我惊讶）这条 SQL 语句在 Access 表单中被视为有效：

  SELECT [tblCustomer]![LastName] AS LastName 
  FROM tblCustomer;

In Access, that is 100% equivalent to this SQL statement:

在 Access 中，这 100% 相当于此 SQL 语句：

  SELECT tblCustomer.LastName 
  FROM tblCustomer;

...so I don't understand why anyone would write it, except if they forgot the context (or never understood it in the first place). It could be a case of aliasing gone wrong, but it's not what I consider good form.

...所以我不明白为什么有人会写它，除非他们忘记了上下文（或者根本就没有理解它）。这可能是混叠出错的情况，但这不是我认为的好形式。

Now, the long answer to the general question of ! (bang) vs. . (dot):

现在，对一般问题的长答案！(砰) vs. . （点）：

In general, in Access, the bang operator delineates the default collection of an object and its items. The dot operator delineates an object and its methods, properties and members.

通常，在 Access 中，bang 运算符描述对象及其项的默认集合。点运算符描述了一个对象及其方法、属性和成员。

That is for Access, and applies to Access objects and the object model for Access.

这适用于Access，适用于 Access 对象和 Access 的对象模型。

But you also use SQL in Access, and so you also have TableName.FieldName in SQL, where the dot operator separates an item in a default collection. TableName.FieldName could be considered to be short for TableName.Fields("FieldName"), as you find with Forms!MyForm!MyControl being equivalent to Forms!MyForm.Controls("MyControl"). But this rule doesn't apply in SQL -- TableName.Fields("FieldName") is not valid SQL, only TableName.FieldName is.

但您也在 Access 中使用 SQL，因此 SQL 中也有 TableName.FieldName，其中点运算符分隔默认集合中的项目。TableName.FieldName 可以被认为是 TableName.Fields("FieldName") 的缩写，因为你发现 Forms!MyForm!MyControl 等同于 Forms!MyForm.Controls("MyControl")。但是这条规则不适用于 SQL —— TableName.Fields("FieldName") 不是有效的 SQL，只有 TableName.FieldName 是。

So, you have to keep straight which paradigm is controlling the namespace you're working in, i.e., whether it's an Access namespace or a SQL namespace.

因此，您必须明确控制正在使用的命名空间的范式，即，它是 Access 命名空间还是 SQL 命名空间。

Forms!MyForm is also equivalent to Forms.Item("MyForm"), so the ultra-long form would be Forms.Items("MyForm").Controls("MyControl"). Note how the bang operator is a shortcut for the longer form version with the dot operator, so the bang operator is quite frequently used in preference to the dot operator. Note also that the longer form ends up being used when you need to refer to an item whose name is stored in a variable, which is not possible with the bang operator:

Forms!MyForm 也等价于 Forms.Item("MyForm")，所以超长表单将是 Forms.Items("MyForm").Controls("MyControl")。请注意 bang 运算符如何是带有点运算符的较长形式版本的快捷方式，因此 bang 运算符比点运算符更常用。另请注意，当您需要引用名称存储在变量中的项目时，最终会使用较长的形式，而 bang 运算符则无法做到这一点：

  Dim strForm As String

  strForm = "MyForm"
  ' This is OK
  Debug.Print Forms(strForm).Controls.Count
  ' This is not
  Debug.Print Forms!strForm.Controls.Count

Also, in VBA code, Microsoft has engineered things to obfuscate this distinction in Forms and Reports, where it used to be that Me!MyFavoriteControl was legal as a control reference, and Me.MyFavoriteControl would have been legal only as a reference to a custom property (or module-level variable, which would be member of the object). You could also unwisely name a function or sub "MyFavoriteControl" and it could be referred to with the dot operator.

此外，在 VBA 代码中，Microsoft 设计了一些东西来混淆 Forms 和 Reports 中的这种区别，以前 Me!MyFavoriteControl 作为控件引用是合法的，而 Me.MyFavoriteControl 仅作为对自定义的引用才是合法的属性（或模块级变量，将是对象的成员）。您也可以不明智地将函数或子命名为“MyFavoriteControl”，并且可以使用点运算符来引用它。

But with the introduction of VBA, MS introduced implicitly-created (and maintained) hidden property wrappers around all controls so that you could use the dot operator. This had one huge advantage, and that is compile-time checking of control references. That is, if you type Me.MyFavoriteControl and there is no control by that name and no other member of any kind with that name within the form/report's namespace, then you would get a compile-time error (indeed, you'd be informed of the error as soon as you left the line of code where you made the error). So, if you had this code:

但是随着 VBA 的引入，MS 在所有控件周围引入了隐式创建（和维护）的隐藏属性包装器，以便您可以使用点运算符。这有一个巨大的优势，那就是对控件引用的编译时检查。也就是说，如果您键入 Me.MyFavoriteControl 并且没有该名称的控件，并且表单/报表的名称空间内没有具有该名称的任何其他成员，那么您将收到编译时错误（实际上，您会在您离开发生错误的代码行后立即通知错误）。所以，如果你有这个代码：

  Debug.Print Me.Control1

... and you renamed Control1 to be MyControl, you'd get an error the next time you compiled the code.

...并且您将 Control1 重命名为 MyControl，下次编译代码时会出现错误。

What could be the downside of compile-time checking? Well, several things:

编译时检查的缺点是什么？嗯，有几件事：

1. code becomes harder for the programmer to understand on sight. In the past, Me!Reference meant an item in the default collection of a form/report (which is a union of the Fields and Controls collections). But Me.Reference could be a control or a field or a custom property or a public module-level variable or a public sub/function or, or, or... So, it sacrifices immediate code comprehensibility.

2. you are depending on implicit behavior of VBA and its compilation. While this is usually an OK thing to do (particularly if you take good care of your code), VBA compilation is very complex and subject to corruption. Over the years, experienced developers have reported that using the dot operator makes code more subject to corruption, since it adds another layer of hidden code that can get out of synch with the parts of the the application that you canalter explicitly.

3. since you can't control those implicit property wrappers, when they go wrong, you have to recreate your module-bearing object from scratch (usually SaveAsText is sufficient to clear the corruption without losing anything).

1. 程序员在视觉上更难理解代码。在过去，Me!Reference 表示表单/报表的默认集合中的一个项目（它是 Fields 和 Controls 集合的联合）。但是 Me.Reference 可以是一个控件或一个字段或一个自定义属性或一个公共模块级变量或一个公共子/函数或，或，或......因此，它牺牲了直接的代码可理解性。

2. 您依赖于 VBA 及其编译的隐式行为。虽然这通常是一件可以做的事情（特别是如果你很好地照顾你的代码），VBA 编译非常复杂并且容易损坏。多年来，有经验的开发人员报告说，使用点运算符会使代码更容易损坏，因为它添加了另一层隐藏代码，可能会与您可以显式更改的应用程序部分不同步。

3. 由于您无法控制那些隐式属性包装器，当它们出错时，您必须从头开始重新创建包含模块的对象（通常 SaveAsText 足以清除损坏而不丢失任何东西）。

So, many experienced developers (myself included) do not use the dot operator for controls on forms/reports.

因此，许多有经验的开发人员（包括我自己）不使用点运算符来控制表单/报表。

It's not such a big sacrifice as some may think if you use a standard set of naming conventions. For instance, with bound controls on forms, a let them use the default names (i.e., the name of the field the control is bound to). If I don't refer to the control in code, I never change its name. But the first time I refer to it in code, I change its name so that the control name is distinct from the name of the field it is bound to (this disambiguation is crucial in certain contexts). So, a textbox called MyField becomes txtMyField at the time I decide to refer to it in code. The only time I'd ever change the field name after code is written is if I somehow decided that the field was misnamed. In that case, it's easy enough to do a Find/Replace.

如果您使用一组标准的命名约定，这并不是一些人可能认为的那样大的牺牲。例如，对于窗体上的绑定控件，允许它们使用默认名称（即控件绑定到的字段的名称）。如果我不在代码中引用控件，我就不会更改它的名称。但是我第一次在代码中引用它时，我更改了它的名称，以便控件名称与其绑定到的字段名称不同（这种消除歧义在某些上下文中是至关重要的）。因此，当我决定在代码中引用它时，名为 MyField 的文本框变成了 txtMyField。我唯一一次在编写代码后更改字段名称的情况是我以某种方式确定该字段的名称有误。在这种情况下，进行查找/替换很容易。

Some argue that they can't give up the Intellisense, but it's not true that you entirely give it up when you use the bang operator. Yes, you give up the "really intelligent" Intellisense, i.e., the version that limits the Intellisense list to the methods/properties/members of the selected object, but I don't need it for that -- I need Intellisense to save keystrokes, and with Ctrl-SPACEBAR you get a full Intellisense list that autocompletes just like the context-specific Intellisense, and can then short-circuit the typing.

有些人争辩说他们不能放弃 Intellisense，但是当您使用 bang 运算符时，您完全放弃它是不正确的。是的，您放弃了“真正智能”的智能感知，即，将智能感知列表限制为所选对象的方法/属性/成员的版本，但我不需要它——我需要智能感知来保存击键，使用 Ctrl-空格键，您将获得一个完整的 Intellisense 列表，该列表会像特定于上下文的 Intellisense 一样自动完成，然后可以短路输入。

Another area of dot/bang confusion is with DAO recordsets in VBA code, in which you use the dot operator for the SQL that you use to open your recordset and the bang operator to refer to fields in the resulting recordset:

点/爆炸混淆的另一个领域是 VBA 代码中的 DAO 记录集，在其中您使用点运算符来表示用于打开记录集的 SQL，并使用 bang 运算符来引用结果记录集中的字段：

  Dim rs As DAO.Recordset

  Set rs = CurrentDB.OpenRecordset("SELECT MyTable.MyField FROM MyTable;")
  rs.MoveFirst
  Debug.Print rs!MyField

  rs.Close
  Set rs = Nothing

If you keep in mind which namespace you're working in, this is not so confusing -- the dot is used in the SQL statement and the bang in the DAO code.

如果您记住您在哪个命名空间中工作，这就不那么令人困惑了——在 SQL 语句中使用了点，而在 DAO 代码中使用了 bang。

So, to summarize:

所以，总结一下：

1. in SQL, you use the dot operator for fields in tables.

2. in forms and reports, you use the bang operator for controls and the dot operator for properties/methods (though you can also use the dot operator, but it's not necessarily advisable).

3. in VBA code, references to controls on forms and reports may use either dot or bang, though the dot may be prone to possible code corruption.

4. in SQL, you may see the bang operator used, but only if there is a reference to a control on an Access form or report, of the form "Form!FormName!ControlName" or "Report!ReportName!ControlName".

5. in VBA code working with DAO recordsets, you may see both the dot and bang operator, the former in defining the SQL that is used to open the recordset, and the latter to refer to fields in the resulting recordset once it is open.

1. 在 SQL 中，您对表中的字段使用点运算符。

2. 在窗体和报表中，您对控件使用 bang 运算符，对属性/方法使用点运算符（虽然您也可以使用点运算符，但不一定可取）。

3. 在 VBA 代码中，对窗体和报表控件的引用可以使用 dot 或 bang，尽管 dot 可能容易导致代码损坏。

4. 在 SQL 中，您可能会看到使用了 bang 运算符，但前提是引用了 Access 窗体或报表上的控件，形式为“Form!FormName!ControlName”或“Report!ReportName!ControlName”。

5. 在使用 DAO 记录集的 VBA 代码中，您可能会看到点和 bang 运算符，前者用于定义用于打开记录集的 SQL，后者用于在结果记录集打开后引用其中的字段。

Is that complicated enough for you?

这对你来说够复杂了吗？

回答by UltraCommit

I think that the esclamation mark is only a conventional separator.

我认为esclamation mark只是一个传统的分隔符。

In Oracle PL/SQL you use dot:

在 Oracle PL/SQL 中，您使用点：

[FETT List].[FETT Search]

[FETT 列表].[FETT 搜索]

Any other clues?!

还有什么线索吗？！