please explain [], . and !
in [Queries]
|
Prev: sum a count column in query
Next: Value in union query
From: Phil Smith on 12 Apr 2010 15:40 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 >> >> [table].[field] >> >> [table].field >> >> table!field >> >> [table]![field] >> >> [table]!field >> >> >> Phil
From: Bob Barrows on 12 Apr 2010 15:55 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 >>> >>> [table].[field] >>> >>> [table].field >>> >>> table!field >>> >>> [table]![field] >>> >>> [table]!field >>> >>> >>> Phil -- HTH, Bob Barrows
From: Bob Barrows on 12 Apr 2010 15:56 I can't answer for MySQL, but brackets are used the same way in MS SQL. Phil Smith wrote: > 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? > > > > > > On 4/12/2010 11:54 AM, ghetto_banjo wrote: >> [table].[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 -- HTH, Bob Barrows
From: Phil Smith on 12 Apr 2010 16:11 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 On 4/12/2010 12:55 PM, Bob Barrows wrote: > 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 >>>> >>>> [table].[field] >>>> >>>> [table].field >>>> >>>> table!field >>>> >>>> [table]![field] >>>> >>>> [table]!field >>>> >>>> >>>> Phil >
From: Bob Barrows on 12 Apr 2010 16:29
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 Smith wrote: > 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 > > > > > > > > On 4/12/2010 12:55 PM, Bob Barrows wrote: >> 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 >>>>> >>>>> [table].[field] >>>>> >>>>> [table].field >>>>> >>>>> table!field >>>>> >>>>> [table]![field] >>>>> >>>>> [table]!field >>>>> >>>>> >>>>> Phil -- HTH, Bob Barrows |