please explain [], . and !

[Phil Smith]

When I look at SQL versions of code created in design view, I see many
different ways of referencing fields in a table. Please explain the
differences and why to use what...


table.field

.[field]

.field

table!field

![field]

!field


Phil

 

[ghetto_banjo]

.[field] should work just about anywhere, and the brackets will
be required if the table or field names have a Space or a # or some
other character which they really shouldn't be using in the first
place. you can omit the brackets (in most cases) if the names of the
tables & fields use alphanumeric only. always using the brackets can
be considered good practice however.


I only use the ! when i am referencing a form. i.e. Forms!
formName.fieldName

 

[John Spencer]

Brackets are used if the table name or field name is a reserved word or
contains any characters other than letters, numbers, and the underscore.

Why? To avoid problems. Simple example: A field named x-y could be
interpreted as subtract y from x, so to make sure the the field is understood
as a field you enter [x-y] which tells the SQL engine that this is a field.
Another simple example Date. Date is a function that returns the current
date, [Date] is a field.

Period versus Exclamation. The preferred method is to use the period when
separating table and field names. The only time I use the ! is when I am
referencing controls and not table and field names.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Phil Smith]

That is kinda what I thought on the brackets. About the Best Practices
thing, though. How does MySql, MS SQL, etc. handle them with
passthrough queries? They safe for most SQL engines?

 

[Marshall Barton]

When I look at SQL versions of code created in design view, I see many
different ways of referencing fields in a table. Please explain the
differences and why to use what...


table.field

.[field]

.field

table!field

![field]

!field

The [ ] are only needed when a name contains a space or
other funky character. If you stick to alphanumeric and
underscore characters, you do not need to use [ ]. OTOH,
Access can't be bothered to figure out if a name needs the
brackets so it always puts them in whether they are needed
or not. I find that kinf of thing rather irritating,
especially the way they toss in all kinds of unneeded ( )

table.field is what the SQL specifications say, but because
so many rookies can't keep it straight, Access often allows
either.

Technically, the ! is the operator for referring to an
element in a collection and . is used when referring to an
object's properties or methods. So, in VBA/DAO, not SQL,
tabledef.Fields!field is correct and because Fields is the
default property of a tabledef, Access VBA will use it even
when you omit it by using tabledef!field. So it's not a
great leap to allow the same thing in a query. Personally,
I think its more confusing to allow different syntax to mean
the same thing so I always use the specified table.field in
a query.

 

[Phil Smith]

Thanks. So is there really no difference between period and exclamation
point, just an aesthetics/visibility issue? Because Access seems to use
it a lot in table. field combinations, and other SQL engines don't seem
to appreciate it...

I am running into some significant speed issues on some reports, and am
beginning to change some of the queries to passthrough queries. It is
not as simple as I would have hoped to convert these over.

Learning a lot though...

Thanx

Brackets are used if the table name or field name is a reserved word or
contains any characters other than letters, numbers, and the underscore.

Why? To avoid problems. Simple example: A field named x-y could be
interpreted as subtract y from x, so to make sure the the field is
understood as a field you enter [x-y] which tells the SQL engine that
this is a field.
Another simple example Date. Date is a function that returns the current
date, [Date] is a field.

Period versus Exclamation. The preferred method is to use the period
when separating table and field names. The only time I use the ! is when
I am referencing controls and not table and field names.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

When I look at SQL versions of code created in design view, I see many
different ways of referencing fields in a table. Please explain the
differences and why to use what...


table.field

.[field]

.field

table!field

![field]

!field


Phil

 

[Bob Barrows]

If you're going to be porting to other database systems as you implied
in the other post, then use periods instead of the exclamation points,
which are more of a VBA artifact.

If you do have references to form objects in your queries, you need to
be aware that those form objects will not be in the scope of the remote
database when used in a passthrough query, so errors will result. You
will need to use VBA to replace the reference to the form object in the
query's SQL property with the actual value of the form object before
running the query.

Thanks. So is there really no difference between period and
exclamation point, just an aesthetics/visibility issue? Because
Access seems to use it a lot in table. field combinations, and other
SQL engines don't seem to appreciate it...

I am running into some significant speed issues on some reports, and
am beginning to change some of the queries to passthrough queries.
It is not as simple as I would have hoped to convert these over.

Learning a lot though...

Thanx

Brackets are used if the table name or field name is a reserved word
or contains any characters other than letters, numbers, and the
underscore.

Why? To avoid problems. Simple example: A field named x-y could be
interpreted as subtract y from x, so to make sure the the field is
understood as a field you enter [x-y] which tells the SQL engine that
this is a field.
Another simple example Date. Date is a function that returns the
current date, [Date] is a field.

Period versus Exclamation. The preferred method is to use the period
when separating table and field names. The only time I use the ! is
when I am referencing controls and not table and field names.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

When I look at SQL versions of code created in design view, I see
many different ways of referencing fields in a table. Please
explain the differences and why to use what...


table.field

.[field]

.field

table!field

![field]

!field


Phil

 

[Bob Barrows]

I can't answer for MySQL, but brackets are used the same way in MS SQL.

That is kinda what I thought on the brackets. About the Best
Practices thing, though. How does MySql, MS SQL, etc. handle them
with passthrough queries? They safe for most SQL engines?

.[field] should work just about anywhere, and the brackets
will be required if the table or field names have a Space or a # or
some other character which they really shouldn't be using in the
first place. you can omit the brackets (in most cases) if the names
of the tables& fields use alphanumeric only. always using the
brackets can be considered good practice however.


I only use the ! when i am referencing a form. i.e. Forms!
formName.fieldName

 

[Phil Smith]

I am aware that I need to put my controls into my final queries
discretely. I have only one problem with that. Dates.

What is the easiest way to convert an Acess date/time field, to a
straight SQL compatible date as I assemble my query?

Phil

If you're going to be porting to other database systems as you implied
in the other post, then use periods instead of the exclamation points,
which are more of a VBA artifact.

If you do have references to form objects in your queries, you need to
be aware that those form objects will not be in the scope of the remote
database when used in a passthrough query, so errors will result. You
will need to use VBA to replace the reference to the form object in the
query's SQL property with the actual value of the form object before
running the query.

Thanks. So is there really no difference between period and
exclamation point, just an aesthetics/visibility issue? Because
Access seems to use it a lot in table. field combinations, and other
SQL engines don't seem to appreciate it...

I am running into some significant speed issues on some reports, and
am beginning to change some of the queries to passthrough queries.
It is not as simple as I would have hoped to convert these over.

Learning a lot though...

Thanx

Brackets are used if the table name or field name is a reserved word
or contains any characters other than letters, numbers, and the
underscore.

Why? To avoid problems. Simple example: A field named x-y could be
interpreted as subtract y from x, so to make sure the the field is
understood as a field you enter [x-y] which tells the SQL engine that
this is a field.
Another simple example Date. Date is a function that returns the
current date, [Date] is a field.

Period versus Exclamation. The preferred method is to use the period
when separating table and field names. The only time I use the ! is
when I am referencing controls and not table and field names.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Phil Smith wrote:
When I look at SQL versions of code created in design view, I see
many different ways of referencing fields in a table. Please
explain the differences and why to use what...


table.field

.[field]

.field

table!field

![field]

!field


Phil

 

[Bob Barrows]

Use this for SQL Server:
Format(<date-time value>, "yyyymmdd hh:mm:ss")
making sure you delimit it with apostrophes (single quotes), so the
query that gets passed through to sql server looks like this:
" ... WHERE somedatecolumn > '20100201 13:00:00'; "

I'm not sure what works for MySQL

I am aware that I need to put my controls into my final queries
discretely. I have only one problem with that. Dates.

What is the easiest way to convert an Acess date/time field, to a
straight SQL compatible date as I assemble my query?

Phil

If you're going to be porting to other database systems as you
implied in the other post, then use periods instead of the
exclamation points, which are more of a VBA artifact.

If you do have references to form objects in your queries, you need
to be aware that those form objects will not be in the scope of the
remote database when used in a passthrough query, so errors will
result. You will need to use VBA to replace the reference to the
form object in the query's SQL property with the actual value of the
form object before running the query.

Thanks. So is there really no difference between period and
exclamation point, just an aesthetics/visibility issue? Because
Access seems to use it a lot in table. field combinations, and other
SQL engines don't seem to appreciate it...

I am running into some significant speed issues on some reports, and
am beginning to change some of the queries to passthrough queries.
It is not as simple as I would have hoped to convert these over.

Learning a lot though...

Thanx



On 4/12/2010 12:27 PM, John Spencer wrote:
Brackets are used if the table name or field name is a reserved
word or contains any characters other than letters, numbers, and
the underscore.

Why? To avoid problems. Simple example: A field named x-y could be
interpreted as subtract y from x, so to make sure the the field is
understood as a field you enter [x-y] which tells the SQL engine
that this is a field.
Another simple example Date. Date is a function that returns the
current date, [Date] is a field.

Period versus Exclamation. The preferred method is to use the
period when separating table and field names. The only time I use
the ! is when I am referencing controls and not table and field
names.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Phil Smith wrote:
When I look at SQL versions of code created in design view, I see
many different ways of referencing fields in a table. Please
explain the differences and why to use what...


table.field

.[field]

.field

table!field

![field]

!field


Phil

 

[Phil Smith]

I think that will work with MySQL as well. Thank you very much.

Use this for SQL Server:
Format(<date-time value>, "yyyymmdd hh:mm:ss")
making sure you delimit it with apostrophes (single quotes), so the
query that gets passed through to sql server looks like this:
" ... WHERE somedatecolumn> '20100201 13:00:00'; "

I'm not sure what works for MySQL

I am aware that I need to put my controls into my final queries
discretely. I have only one problem with that. Dates.

What is the easiest way to convert an Acess date/time field, to a
straight SQL compatible date as I assemble my query?

Phil

If you're going to be porting to other database systems as you
implied in the other post, then use periods instead of the
exclamation points, which are more of a VBA artifact.

If you do have references to form objects in your queries, you need
to be aware that those form objects will not be in the scope of the
remote database when used in a passthrough query, so errors will
result. You will need to use VBA to replace the reference to the
form object in the query's SQL property with the actual value of the
form object before running the query.

Phil Smith wrote:
Thanks. So is there really no difference between period and
exclamation point, just an aesthetics/visibility issue? Because
Access seems to use it a lot in table. field combinations, and other
SQL engines don't seem to appreciate it...

I am running into some significant speed issues on some reports, and
am beginning to change some of the queries to passthrough queries.
It is not as simple as I would have hoped to convert these over.

Learning a lot though...

Thanx



On 4/12/2010 12:27 PM, John Spencer wrote:
Brackets are used if the table name or field name is a reserved
word or contains any characters other than letters, numbers, and
the underscore.

Why? To avoid problems. Simple example: A field named x-y could be
interpreted as subtract y from x, so to make sure the the field is
understood as a field you enter [x-y] which tells the SQL engine
that this is a field.
Another simple example Date. Date is a function that returns the
current date, [Date] is a field.

Period versus Exclamation. The preferred method is to use the
period when separating table and field names. The only time I use
the ! is when I am referencing controls and not table and field
names.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Phil Smith wrote:
When I look at SQL versions of code created in design view, I see
many different ways of referencing fields in a table. Please
explain the differences and why to use what...


table.field

.[field]

.field

table!field

![field]

!field


Phil