Solved

Bang vs. Dot in SQL statements

Posted on 2010-08-22
70
649 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:Milewskp
  • 29
  • 18
  • 17
  • +2
70 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33495042
0
 
LVL 84
ID: 33495268
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 300 total points
ID: 33495714
"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
 
LVL 75
ID: 33496640
I should add it require reading if you really want to understand what's going on :-)

mx
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 33497079
<"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
 
LVL 75
ID: 33497097
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
 
LVL 84
ID: 33497282
<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
 
LVL 75
ID: 33497998
:-)
0
 
LVL 1

Author Comment

by:Milewskp
ID: 33503317
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
 
LVL 75
ID: 33503347
Did you read the post by Leigh Purvis in the link I posted ?

mx
0
 
LVL 1

Author Comment

by:Milewskp
ID: 33503383
You also have to use bang whenever you are referring to a form control in a stored query.
0
 
LVL 1

Author Comment

by:Milewskp
ID: 33503391
Hi mx,
<Did you read the post by Leigh Purvis in the link I posted ?>
Yes.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 300 total points
ID: 33503402
"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
 
LVL 1

Author Comment

by:Milewskp
ID: 33503539
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
 
LVL 1

Author Comment

by:Milewskp
ID: 33503547
Oops,
Here's the mdb...
db31.mdb
0
 
LVL 75
ID: 33503589
What you uploaded has zero bytes.

mx
0
 
LVL 1

Author Comment

by:Milewskp
ID: 33503628
I'll try again...

db31.mdb
0
 
LVL 75
ID: 33503730
It works for me ...

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

mx


0
 
LVL 75
ID: 33503777
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
 
LVL 1

Author Comment

by:Milewskp
ID: 33503869
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
 
LVL 75
ID: 33503921
Try what I said @ http:#a33503777

mx
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 100 total points
ID: 33503987
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
 
LVL 75
ID: 33504029
"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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33504094
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
 
LVL 75
ID: 33504113
lol.
0
 
LVL 1

Author Comment

by:Milewskp
ID: 33504295
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
 
LVL 75
ID: 33504331
"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
 
LVL 1

Author Comment

by:Milewskp
ID: 33504487
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
 
LVL 75
ID: 33504577
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33506988
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
 
LVL 1

Author Comment

by:Milewskp
ID: 33511401
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
 
LVL 75
ID: 33513192
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
 
LVL 75
ID: 33513202
Actually ...

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

mx
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33513299
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:Milewskp
ID: 33513317
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
 
LVL 75
ID: 33513359
"Are you saying that this wouldn't work:"
I'm saying it *does* work ...

mx
0
 
LVL 1

Author Comment

by:Milewskp
ID: 33513535
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
 
LVL 75
ID: 33513637
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
 
LVL 75
ID: 33513697
@ 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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33513754
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
 
LVL 75
ID: 33513900
"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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33514466
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
 
LVL 75
ID: 33514582
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
 
LVL 1

Author Comment

by:Milewskp
ID: 33514649
Hi mx,
If you're interested in consistency, why not use bangs - they always work, whereas dots don't.
0
 
LVL 75
ID: 33514704
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33515209
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
 
LVL 75
ID: 33515279
Well, I wasn't referring to those cases. More in line with the examples I gave ... and many other places.

mx
0
 
LVL 1

Author Comment

by:Milewskp
ID: 33515561
Thanks everyone, my original question has been answered. I'll split the points.
0
 
LVL 75
ID: 33515833
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
 
LVL 1

Author Comment

by:Milewskp
ID: 33520534
Here's my summary...

Dot-vs.Bang-vs.-P-Q.doc
0
 
LVL 84
ID: 33521537
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33521606
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
 
LVL 1

Author Comment

by:Milewskp
ID: 33524155
Thanks LSM and Leigh, I really appreciate the feedback!
0
 
LVL 75
ID: 33524642
I think we now have THE Definitive Document on the subject.  The two of you should publish this in the Articles section.

mx
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33524732
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
 
LVL 75
ID: 33524779
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33524869
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
 
LVL 75
ID: 33524898
OOPS .... TYPO:


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

mx
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33524916
Yeah I'd assumed as much. (Before disagreeing with including the subsequent opinion ;-)
0
 
LVL 75
ID: 33524965
Thus:

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

Correct ?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33524981
Aye.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33570940
No further version of the document yet? :-)
0
 
LVL 75
ID: 33570964
Holding my breath too ...

0
 
LVL 1

Author Comment

by:Milewskp
ID: 33571069
Hi Gents,
Here it is...

DBPQ.Rev1.doc
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33571144
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33575882
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
 
LVL 1

Author Comment

by:Milewskp
ID: 33577249
Hi Leigh,
Thanks for the comments. How do I go about publishing this as an EE article?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33577300
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33578101
>> 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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now