please explain [], . and !
in [Queries]
|
Prev: sum a count column in query
Next: Value in union query
From: Phil Smith on 12 Apr 2010 14:14 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: ghetto_banjo on 12 Apr 2010 14:54 [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
From: John Spencer on 12 Apr 2010 15:27 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: Phil Smith on 12 Apr 2010 15:36 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 > > > >
From: Marshall Barton on 12 Apr 2010 15:37
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 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. -- Marsh MVP [MS Access] |