Exclamation Marks in a Query SQL

Question

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

Can someone explain what the exclamation mark means?

Thanks

Nereus

Please add the appropriate tags to your question. I added ms-access, because I believe this is in Access, but I might be wrong. Please make sure it's okay. – Nucleo

There is nothing version 2007-specific about this question, so really, ms-access is an adequate tag (though one might mention version 2007 in the question). – Alo

Answer 1

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.

Monecious

The ! is a non-standard way to specify fields in SQL statements in Access. It's handled, but now well, as [Table]![Field] is assigned an on-the-fly alias within the QBE, while Table.Field will inherit the fieldname (i.e., no aliasing). – Alo

Answer 2

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.

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

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

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

  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):

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.

That is for Access, and applies to Access objects and the object model for 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.

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.

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:

  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.

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:

  Debug.Print Me.Control1

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

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 can alter 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).

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.

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.

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:

  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.

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.

Is that complicated enough for you?

Alo

This is the longest answer I think I've ever seen on SO – Aila

I've written much longer than that! – Alo

Answer 3

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

In Oracle PL/SQL you use dot:

[FETT List].[FETT Search]

Any other clues?!

Practiced

Does Oracle allow a space in the name of a table/column? I think in Oracle it would probably be FETT_List.FETT_Search. – Strow

Yes, Oracle allows spaces in the name of a table or a column, BUT you have to enclose the entire string between quotation marks, as in the following example: CREATE TABLE goofy ("my column" VARCHAR2 (1000)); – Practiced

Please note tha space between the word <my> and the word <column> – Practiced

Another example: CREATE TABLE "super mario land" ("my column" VARCHAR2 (1000)); – Practiced

In Jet/ACE SQL, the brackets are used for fields with spaces or unconventional characters in them. The ! is nonstandard usage, though Access adapts to the nonstandard and interprets it semi-correctly. – Alo