What do "!" mean in a query

[billycairn]

Hi

can someone please explain what the "!" mean in the code below.

SELECT Processing.[Parameter ID], Processing.Values, Processing.Less, Processing.Greater, Processing.[Detection Limit], [processing]![Values]<[processing]![Detection Limit] AS Expr1

FROM Processing

WHERE ((([processing]![Values])<[processing]![Detection Limit]));

Much appreciated

BC

 

[lupins46]

It's just a linking symbol from one object to another.
You are saying 'on the table [Processing] use the field [Values]'.

There used to be strict rules about where to use '!' and where to use '.' but the differences in usage have almost disappeared now.

 

[jrbarnett]

Actually, there is a difference when it is used on a bound form/report. It is as follows:

The . refers to the control on a bound form/report but the ! refers to the value in the underlying data.

They will be exactly the same unless you update the data, in which case the . value will hold the new version until the AfterUpdate event has fired (at which time it has been saved back to the table) and the ! will hold the old value prior to the AfterUpdate event.

On adding a new record, the . value will hold an entry and the ! will be empty until the AfterInsert event handler fires.

I hope that this clarifies the situation.

John

 

[billycairn]

Ta Lupins

I find it curious that both (dot and bang) should be used in the same query though. Strikes me as a bit inconsistant (I didn't write it).

 

[billycairn]

John

thank you. That makes more sense.

 

[MajP]

Actually it should not make sense since the replies are wrong. This is one of many reasons I recommend to stop using the bang operator. There is only one place you must use it, and that is in a Parameter query. I can think of about five other reasons why not to use it. Mainly it is sloppy shorthand that hides what is really going on.
The real name of the Bang operator is the Dictionary Lookup Operator. It works in one place only, to reference an object in a DEFALT COLLECTION with a NAMED index. For example take a Form object. The Form object has several collections, but the default collection is the Controls collection. Controls have string named indices. So in complete notation, with Me returning the instantiated object:

Me.Controls.index(“namedIndexOftheControl”).value

This says get the controls collection of the form, use the index method to return the named control from the Controls collection and get its value. By looking at this you know exactly what is happening. Now VB is sloppy and “index” is a default method, and value is a default property. The controls collection is the default collection, and it is indexed by strings. So in shorthand

Me!namedIndexOftheControl

Same with a recordset. The default collection is Fields. So

myRS.fields.index(“fieldname”).value
‘can be written as
MyRs!fieldname

Conversely to what was suggested, it does not link objects. Take the Recordsetclone of a from. You could not write
Me!Recordsetclone

It also has nothing to do with the control versus the value in the field. If you have a bound form with a control the same as your field name, when you add the control to the form it adds an object to the default Controls collection.
Me!fieldname
Is shorthand for
Me.Controls.index(“fieldname”).value
And returns the value of the control in the controls collection with index “fieldname”
Now when you drop a control on the form it is also given a property with the field’s name. So
Me.fieldname
Returns the value of the property. You can see this by dropping a field on a form and calling the control something different such as “txtBoxFieldName”. Look at the intellisense and see the difference between
Me.fieldname
And
Me!txtBoxFieldName

I hope this helps