Prev: Douglas Steele, Arvin Meyer, Tony Toews, John Spencer, Duane H
Next: Hide or Disable Add New Record Button
From: KenSheridan via AccessMonster.com on 30 May 2010 18:50
Dan:

In SQL the table name needs to be used to qualify the column name if there
can be any ambiguity as to which column is referred to. This would be the
case if two tables are joined on columns of the same name and one of those
columns is included in the query, e.g. in a table which joins Customers to
Orders:

SELECT OrderID, OrderDate, Customers.CustomerID, Customer
FROM Orders.INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

In the above the OrderID and OrderDate columns appear only in the Orders
table, and the Customer column appears only in the Customers table, so these
do not need qualifying with the table names. CustomerID on the other hand
appears in both tables, as the primary key of Customers and as a foreign key
in Orders, so needs to be qualified with the table name so it's clear which
one is referred to. In this case it would make no difference which table
name is used of course, as the value is the same in each. When a query is
created in design view the table names are inserted automatically in the SQL
even if not required.

Another circumstance in which it is advisable, and possibly necessary, to
qualify a column with the table name is where the column name might be
confused with something else. If Date is used as a column name for instance
then it could be confused with the built in date function, so it would be
advisable to qualify it with the table name to distinguish it from the
function. However, such 'reserved words' should not really be used as object
names; something more specific like OrderDate should be used.

Object names need to be wrapped in square brackets if they include a space or
a special character such as the # character. Otherwise the square brackets
are unnecessary. If in doubt wrap an object name in square brackets; it does
no harm even if its unnecessary. You'll find that in many situations where
object names are entered via the visual interface Access will automatically
wrap them in brackets for you.

Most experienced developers avoid object names with spaces or special
characters, however. If you want to separate two elements of a name use the
underscore character, e.g. Order_Date. There are various conventions
employed, and it's really a matter of personal choice which is used, but I
favour CamelCase where each element of a composite name begins with an upper
case character, as in OrderDate.

Another thing you might have noticed is that if you design a query in design
view, and then switch to SQL view, Access will have inserted parentheses all
over the place. Many of these won't be necessary to enforce the required
logic, though some may be. In John's query for instance it is necessary to
enclose the first join in parentheses; other favours of SQL differ in this
regard, however. As with the automatic insertion of square brackets it's a
case of Access making sure, even where not strictly necessary.

In SQL to qualify a column name with the table name, what you'd call a period
and we'd call a full stop is used, Customers.CustomerID, though back in the
days when I used dBASE it was always called the dot operator, which is pretty
self-explanatory.

The exclamation mark (aka 'bang' on your side of the pond I believe) is used
to qualify a member of a collection, most commonly a control in a form or
report, e.g. Forms!frmCustomers!txtCustomerName. In this frmCustomers is a
member of the database's Forms collection, and txtCustomerName is a member of
the form's Controls collection. For some time now, however, the dot operator
has usually been recommended in such cases over the exclamation mark: Forms.
frmCustomers.txtCustomerName. This is really shorthand for: Forms.Item
("frmCustomers").Controls("txtCustomerName").Value. The short version can be
used because Item, Controls and Value are the default properties in each case,
so can be omitted. It could also be written as Forms("frmCustomers")
("txtCustomerName") therefore.

It used to be the case in earlier versions of Access that the exclamation
mark had to be used when a control was referenced as a parameter in a query,
but in Access 2007 at least it seems to be the case that the dot operator can
now be used.

Other punctuation marks are used in various situations. The comma is used to
separate values in a value list, e.g. with the IN operator in a query for
instance: WHERE City IN("London", "Paris", "New York"). The semi-colon is
used in some cases, e.g. in a combo or list box's RowSource property where
the RowSourceType is a value list "London";"Paris";"New York". In this case
if you enter the list in a control's properties sheet and use commas Access
will automatically convert them to semi-colons.

Ken Sheridan
Stafford, England

Dan wrote:
>Ken, I appreciate the help. I will try this out as soon as I get a chance.
>
>You would think it is pretty straightforward, but I have read MANY posts
>here on this discussion group, to try to figure this out on my own, before
>posting here. As well as the Access help files and other web sites with
>tutorials, etc.
>
>Part of what is making this more confusing for me is that nobody ever posts
>the square brackets, and Access seems to require them. For example, you
>wrote:
>
>RowSource: SELECT ID, City FROM tblCities ORDER BY City;
>
>But it seems that Access prefers that I enter something like this:
>
>RowSource: SELECT [ID], [City] FROM [tblCities] ORDER BY [City];
>
>Ok, I'm getting the hang of it myself, but sometimes it requires the table
>name, and if I'm specifying a column, like for a bound text box, then even
>the word "column" has to be in brackets. For example:
>
>=[Combo24].[Column](2)
>
>Like I said, I'm starting to figure out where brackets are needed and where
>they're not, but some posts, like John's query in this thread - I spent hours
>trying to get it right in Access, and I just can't figure out where the
>brackets go, and where I should be inserting comma's, semicolons, periods, or
>exclamation points, between the parts of the expressions.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201005/1

From: Dan on 31 May 2010 08:39
Ken,
Thank you very much for the specific help related to my situation, and ALSO
for this wealth of information about the full stop, square brackets, and so
on.

Dan
First  |  Prev  | 
Pages: 1 2 3
Prev: Douglas Steele, Arvin Meyer, Tony Toews, John Spencer, Duane H
Next: Hide or Disable Add New Record Button