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 said: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 said: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 said: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 said: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
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 said: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
Phil said: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 said: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
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
Phil said: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