Bang vs. Dot in SQL statements

When a query is based on muliple tables, field names can be ambiguous (eg.,if  two tables each have a field named 'Student'). When I run into this situation I've always used [tablename]![fieldname] to resolve the ambiguity. I've discovered, however, that [tablename].[fieldname] also seems to work.

When should I use one instead of the other?
LVL 1
MilewskpAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
"The only time you'd need the Bang is when you refer to a form in a query "
However, dots work.

*Required* Reading  Full discussion here:

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=84&Number=1645019 

See posts by EE's Leigh Purvis.

mx
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The only time you'd need the Bang is when you refer to a form in a query or controlsource. Otherwise, as cyberkiwi suggests, use the dot.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I should add it require reading if you really want to understand what's going on :-)

mx
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
<"The only time you'd need the Bang is when you refer to a form in a query "
However, dots work.>

Not always. If you build a Recordsource for a form at runtime (a common occurrence), you may find that Dots don't properly refer to the form's recordsource (depending on what you're doing and when you're doing it). The Bang will always work in those instances. Same goes when you refer to a form in the ControlSource of a control, for example:

=Forms!SomeForm!MyControl

If you use the Dot, and you don't take care to rename your control, you may find that your code attempts to refer to a Field in the form's Recordset (which may or may not be present. In most cases it'll work, since Access is generally intelligent enough to figure out what's going on, but IMO you're better off using the Bang in these circumstances.

I've also seen instances where referring to a Form in the Criteria row of a query, and using the Dot syntax, causes the query to fail. Again, I believe this was due to (a) naming and (b) the timing of the Recordsource, but still the Bang fixed the matter.


0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Can't say I ever seen it fail, but I guess there is always the first time.

Nonetheless, it does work except in some extreme cases - maybe.

However, it's not that big of a deal in queries or control sources since Intellisense don't apply there anyway.

mx

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<Nonetheless, it does work except in some extreme cases - maybe.>

That's sorta like saying "The car works fine unless you turn to the left". Either it works or it doesn't.

I'm not saying you should use Bang over Dot - I certainly don't, and use the Dot everywhere I can - but instead I'm saying there are cases where you need to be aware that the Dot could be misinterpreted. I realize that you may have not encountered this, but that doesn't mean it can't happen.

Note too that you must use the Bang format when working with Query parameters.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
:-)
0
 
MilewskpAuthor Commented:
Thanks all!

Here's a summary of what I've learned about Dot vs. Bang vs. Parentheses-Quote. Please comment if you see errors or omissions:

WHAT         FUNCTION
----------------------------------------------------------------------------------------
Dot        Separates an object from a property, method or collection.      
P-Q         Specifies a member of a collection.       
Bang        Specifies a member of the default collection.
----------------------------------------------------------------------------------------

CONCLUSIONS:
- Use dot to separate an object and its properties/ methods, and to specify controls.
- Use parentheses-quote to specify a member of a collection.
- Never use bang unless you have to, ie:
    - To access a recordsource field that has the same name as a control; eg.,  Me!ID.
    - For query parameters that refer to form fields; eg., qd.Parameters("ID:") = Forms!FormName!SubFormName.Form.MyControl
    - For the Link Master Fields property of a subform; eg.,  Me.MySubForm.LinkMasterFields = Forms!FormName!SubFormName.Form.MyControl
    - For controlsource properties that refer to form fields; eg., Me.ID.ControlSource = Forms!FormName!SubFormName.Form.MyControl


0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Did you read the post by Leigh Purvis in the link I posted ?

mx
0
 
MilewskpAuthor Commented:
You also have to use bang whenever you are referring to a form control in a stored query.
0
 
MilewskpAuthor Commented:
Hi mx,
<Did you read the post by Leigh Purvis in the link I posted ?>
Yes.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
"Yes"
Then you win :-)

"You also have to use bang whenever you are referring to a form control in a stored query."
False.

mx
0
 
MilewskpAuthor Commented:
Hi mx,
<You also have to use bang whenever you are referring to a form control in a stored query. "  False.>

I've attached a test mdb file. Open the file, then open the form and then open the query. You should find that the query opens without error.  I was not able to get the query to work unless I used bang.

I get the same result when I try reference a form control in an expression for any row in the query grid.
0
 
MilewskpAuthor Commented:
Oops,
Here's the mdb...
db31.mdb
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What you uploaded has zero bytes.

mx
0
 
MilewskpAuthor Commented:
I'll try again...

db31.mdb
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
It works for me ...

SELECT Table1.Parent, [forms].[Form1].[child] AS Child
FROM Table1;

mx


0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
However, to avoid issues *in general* ... give your text box (in the example) a name like

txtChild

and change the query to:

SELECT Table1.Parent, [forms].[Form1].[txtchild] AS ThChild
FROM Table1;

mx
0
 
MilewskpAuthor Commented:
Hi Mx,
<It works for me ...
SELECT Table1.Parent, [forms].[Form1].[child] AS Child
FROM Table1;>
When I try this it treats [forms].[Form1].[child] as a parameter, not a reference to the Child control.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try what I said @ http:#a33503777

mx
0
 
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
For what it's worth, my opinion on this (which is, at times, rather strong I admit) is:
>>  Never use bang unless you have to
I'd have to disagree.
I can't think of a particularly compelling argument to use Forms.FormName.ControlName over Forms!FormName!ControlName.
Especially if you consider, in code (not yet limited to query implementations) an expression such as:
Forms.FormName.FieldName
Where, much more preferable is:
Forms!FormName!FieldName
Back to queries.
So - continuing on I still see no reason to avoid the appropriate syntax (following established expression building axioms as you've correctly summarised earlier). i.e. use Bangs where appropriate.
SELECT * FROM TableName WHERE FieldName = Forms!FormName!ControlName
Note the flip side to this (and where my opinion becomes less humble ;-).
I hate (hate hate hate hate) use of bangs in data objects.
SELECT TableName!FieldName FROM TableName
makes me want to crawl (not walk - because I'm too physically ill to then stand up) to the sick bucket.
That it is still resolved can be nothing more than a peculiarity of Jet's support of expressions.
It's utterly non-ANSI standard and just alienates Jet/ACE from the rest of the DBMS market.
It honestly annoys me no end.
Still... there it is.
Cheers.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"I can't think of a particularly compelling argument to use Forms.FormName.ControlName over Forms!FormName!ControlName."

Well, I agree.  I was just stating that it ... does work.

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
At a Clay Pigeon (Skeet) Shooting ground once, years ago, I observed a shooter at a stand ready. Before shouting "Pull", he decided to check that either his barrels weren't obstructed or what choke he had in there. Whichever the case, he performed this check by sticking two fingers into the ends of the barrels of his loaded gun.
I could think of no particularly compelling reason to do that.
;-)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
lol.
0
 
MilewskpAuthor Commented:
Hi mx,
Yes that works. Thanks!

But I don't understand why it ([forms].[Form1].[txtchild]) works: txtchild is not a property, method or collection of Form1, and Form1 is not a property, method or collection of Forms. Yet dot seems to work.

Can you explain?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"txtchild is not a property, method or collection of Form1,"

In fact it is. Any control you add to a Form becomes a Property of that form.

mx

0
 
MilewskpAuthor Commented:
Hi mx,
Yes, you're right of course; what i meant to say was that Form1 is not a property, method of collection of Forms. It is a member of the forms collection (ie Forms("Form1")).
The only properties/ method/ collections of Forms are: Application, Item, Count, Parent.

So why does this syntax work?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Probably because of what Leigh mentioned @ http:#a33503987

"That it is still resolved can be nothing more than a peculiarity of Jet's support of expressions."

Leigh ?

mx

0
 
Leigh PurvisDatabase DeveloperCommented:
There's no way to know for sure - but it's reasonable to consider that Access treats expressions of its UI objects differently as it resolves them.
There's no obvious evidence that it embues those collections with some new implicit property as controls are on a form.
More that, as the expression is parsed (down its object hierarchy) then the Forms and Reports collections are expanded differently within the namespace (a word Markus uses to describe expression object scope which I've always agreed with and liked).

I, personally, am unaware of any other Access collections which behave this way. As the expression service behaves differently (or indeed technically is different) running within Access then this divergence of collection behaviour could be a deliberate implementation - or, as mentioned above, simply a result of (overly) "enthusiastic" resolution of an expression (i.e. attempting to evaluate it "no matter what"). But even that requires some special treatment of those collections. Whether that's a hidden property is hard to say. But that could be a lot of hidden properties! It doesn't seem that would have been a good choice for implementation. But perhaps that decision was made to be inkeeping with the control level implementation.
If you want to wrap that up into a neat bundle (which is still a black box) the Forms and Reports collections are evaluated differently.
It's as simple and enigmatic as that I reckon.
0
 
MilewskpAuthor Commented:
Hi LSM,
<Note too that you must use the Bang format when working with Query parameters.>
I thought so too, but can't think of an example. The only two ways that query parameters are used (that I know of) are: manually and in code. In neither case do you have to use bang.
Please explain.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Here is your example:

Public Function mQyrParmTest() As Boolean
   
    Dim rst As DAO.Recordset, qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("qryParametersTest")
    With qdf
        .Parameters("[Forms]![frmTest].[txtEmpID]") = ([Forms]![frmTest].[txtEmpID])
        ' or Parameters(0) = ([Forms]![frmTest].[txtEmpID]) ' ***********************************HERE
        Set rst = .OpenRecordset(dbOpenDynaset)
        MsgBox rst![EmpName]    'this will display the Employee name related to the ID currently on the form
    End With
    Set rst = Nothing
    Set qdf = Nothing
End Function

I even made a note to myself:

"Note that wherein I use Form. instead of Form! almost everywhere, this is one place where the bang symbol (!) is necessary."

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Actually ...

   .Parameters("[Forms]![frmTest].[txtEmpID]") = ([Forms]![frmTest].[txtEmpID])  ' ****************HERE
                                   ^^                                                ^^^

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
Not that I've gone mad and have deviated from my previous viewpoint, but you should be able to just drop the square brackets to allow "normal" evaluation.
i.e.
.Parameters("Forms.frmTest.txtEmpID") = Forms.frmTest.txtEmpID
or
.Parameters("[Forms].[frmTest].[txtEmpID]") = Forms.frmTest.txtEmpID
0
 
MilewskpAuthor Commented:
Hi mx,
Are you saying that this wouldn't work:

.Parameters("[Forms]![frmTest].[txtEmpID]") = ([Forms("frmTest").[txtEmpID])
        ' or .Parameters(0) = (Forms("frmTest").[txtEmpID]) ' ***********************************HERE

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Are you saying that this wouldn't work:"
I'm saying it *does* work ...

mx
0
 
MilewskpAuthor Commented:
Hi mx,
Then you misread my post.
LSM said that <you MUST use the Bang format when working with Query parameters>.
I asked for an explanation and an example of where this is true. You provided an example of where it is false (ie, bang can be used but is not mandatory).
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Leigh ...

Well, I just tried the following combinations - which do not work:
(and got Item Not Found In This Collection ...)

        .Parameters("[Forms]![frmParametersTest].[txtEmpID]") = ([Forms].[frmParametersTest].[txtEmpID])
       
        .Parameters("[Forms].[frmParametersTest].[txtEmpID]") = Forms.frmParametersTest.txtEmpID

        .Parameters("Forms.frmParametersTest.txtEmpID") = (Forms.frmParametersTest.txtEmpID)

        .Parameters("Forms.frmParametersTest.txtEmpID") = Forms.frmParametersTest.txtEmpID



However, this works:
        .Parameters("[Forms]![frmParametersTest].[txtEmpID]") = Forms.frmParametersTest.txtEmpID


So, any combination with Dots on the left side >> .Parameters( .....) does not work.


These also works:

.Parameters(0) = ([Forms]![frmParametersTest].[txtEmpID])
.Parameters(0) = Forms.frmParametersTest.txtEmpID  


mx

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
@ Miles:

>>   LSM said that <you MUST use the Bang format when working with Query parameters>.
>> "I asked for an explanation and an example of where this is true."

It appears to be *true* in the example I posted (and subsequent tests).

"You provided an example of where it is false (ie, bang can be used but is not mandatory)."
False?  No ... True.  The Bang (appears) to be required.  I say ' appears' now because Leigh is suggesting that is does not, but ... I just ran the tests above ... and ....?

mx

0
 
Leigh PurvisDatabase DeveloperCommented:
Taking the example:
    .Parameters("Forms.frmParametersTest.txtEmpID") = Forms.frmParametersTest.txtEmpID

Did you, naturally, change the parameter definition in the querydef to match?
i.e.
    SELECT * FROM TableName WHERE FieldName = Forms.frmParametersTest.txtEmpID

Or
    PARAMETERS Forms.frmParametersTest.txtEmpID Long;
    SELECT * FROM TableName WHERE FieldName = Forms.frmParametersTest.txtEmpID
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Did you, naturally, change the parameter definition in the querydef to match?"
Ahhh, well ... ahhh ... embarrassingly ... NOOOOO!

OK ... now it *does* work.

This was the only place I thought Bang did not work either.  So, I guess there is not really anyplace they do not work ... relative to the places we thought they did not ...

RE:
"Can't think of a particularly compelling argument to use Forms.FormName.ControlName over Forms!FormName!ControlName."

Well, I can .... think of just one now:
For ... consistency, since it appears Dots do work everywhere.  Again, I believe this has changed over the years.

mx  
0
 
Leigh PurvisDatabase DeveloperCommented:
Bah. Consistency Schmistency.
:-p
It's just expression construction.
There are very predictable rules to follow and you can't go wrong.
No need to attempt to adopt a single separator throughout.
Where, specifically, were you thinking that bangs aren't consistent?
(You mean in that they only represent that which they're supposed to? ;-)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Wasn't saying bangs are not consistent.  Instead, I was saying that since dots work in a least one place where I thought they did not, then for consistency ... use them instead of Bang.  BUT ... I'm not really promoting that.

mx
0
 
MilewskpAuthor Commented:
Hi mx,
If you're interested in consistency, why not use bangs - they always work, whereas dots don't.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Simple.  Because Bangs do not give you Intellisense, as in

Me!txtABC = "XYZ"  ' No intellisense
Me.txtABC = "XYZ"  ' Intellisense

and hundreds of other places.

"whereas dots don't."

And where is that now ?

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
I like intellisense as much as the next guy (unless the next guy likes it in an unhealthy way - I mean it's fine and all - but intellisense and I aren't going steady or anything..)
But it's not as if you get its benefit across the board. For example, type:
Forms.
and what do you get? Exactly what you should - the four properties of the built in collection. Hardly productive in 99% of cases.
A recordset will give you no more help.
Only a form or report with the design time created properties have that advantage. So use dot then.
But with the usual caveat (as I mentioned earlier). Refering to an implicit field property can come back to bite you if you don't go the Bang (or collection member) route.
Cheers.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, I wasn't referring to those cases. More in line with the examples I gave ... and many other places.

mx
0
 
MilewskpAuthor Commented:
Thanks everyone, my original question has been answered. I'll split the points.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
So, why don't we re-summarize where ... Dots do ... for sure ... *not* work ...?  

Bangs vs Dots is a seemingly endless discussion, with each subsequent discussion bring to light one more tiny piece of the puzzle .... :-)

mx
0
 
MilewskpAuthor Commented:
Here's my summary...

Dot-vs.Bang-vs.-P-Q.doc
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure I agree that the Dot is slightly faster, but I think you'd be hard pressed to find any quantative measurements with today's machines. My understanding is that Access/VBA converts to the Parentheses+Quotes method behind the scenes regardless of what syntax you use, thus the P+Q method would be slightly faster (since no conversion would needed).
0
 
Leigh PurvisDatabase DeveloperCommented:
I'm afraid I have a few more suggestions/corrections for the document than that.
Rather than post them here, to be effective, I've edited it (with Track changes) and made comments. (In a bit of a hurry though)
Enjoy! (Or not :-).
DotvsBang2.doc
0
 
MilewskpAuthor Commented:
Thanks LSM and Leigh, I really appreciate the feedback!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I think we now have THE Definitive Document on the subject.  The two of you should publish this in the Articles section.

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
Well, no offense to what Milewskp has so far, but there's a fair bit of editing to do yet before that document is definitive.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Of course ... I really meant that >> It could become the document.

I would also note RE:

"1.2      Special Case
Controls and the fields of a (form's/ report's) recordsource are (1) properties, and also (2) members of the default collection. So, for them you can use dot, bang or P-Q:"

.... that bang will give you Intellisense ... and for me is preferred, unless I need to use a variable.

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
I have no problem with Bang in that capacity. I often use it for that very reason myself.
But in such documents (and even forum posts to an extent) I feel we need to be careful about including preferences. They're almost inevitably subjective. (I try to make clear when I'm expressing what I consider fact and when I'm giving my opinion - for a fairly taciturn guy, I do tend to do the latter more assertively. I figure if people ignore the facts, then that's their own problem ;-).

For someone to whom intellisense isn't important, the late binding of bang might be the definitive reason.
Just accurately stating the advantages and disadvantages and limitations (as already begun in the document) is the way to go. People can decide for themselves.
We wait with baited breath for the next version!
(No pressure... ;-)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
OOPS .... TYPO:


.... that DOT will give you Intellisense ... and for me is preferred, unless I need to use a variable.
            ^^^^

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
Yeah I'd assumed as much. (Before disagreeing with including the subsequent opinion ;-)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Thus:

"I have no problem with DOT in that capacity. "

Correct ?
0
 
Leigh PurvisDatabase DeveloperCommented:
Aye.
0
 
Leigh PurvisDatabase DeveloperCommented:
No further version of the document yet? :-)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Holding my breath too ...

0
 
MilewskpAuthor Commented:
Hi Gents,
Here it is...

DBPQ.Rev1.doc
0
 
Leigh PurvisDatabase DeveloperCommented:
Man that was fast once you got going again. ;-)
Slightly caught on the hoof - I'm not even in a position to pull it apart, erm I mean review it, again just yet. ;-)

I'll try to get back to this later tonight.
Cheers!
0
 
Leigh PurvisDatabase DeveloperCommented:
Much better. Small comments attached. (The document page setup is always way too narrow for me to read the wide tables when I receive it - just thought I'd mention that here).

DBPQ.Rev1LP.doc
0
 
MilewskpAuthor Commented:
Hi Leigh,
Thanks for the comments. How do I go about publishing this as an EE article?
0
 
Leigh PurvisDatabase DeveloperCommented:
You're asking the wrong fella there I'm afraid.
Markus or Patrick are your best bet most likely. But I'll ask someone to swing on by in an advisory capacity.
0
 
Leigh PurvisDatabase DeveloperCommented:
>> Markus (harfang) has been pretty active as an Access Page Editor
Heh, you get two grillings for the price of one then. You've had the first part from me so far, time for the second half of the old double act. :-D
(Just kidding - it'll be great by the time it's published).
0
All Courses

From novice to tech pro — start learning today.