When to use [ ]'s, periods, exclamation points, etc

[thardyjackson]

Is there a good resource with guidance on when to use brackets, periods, exclamation points, etc.? For example ....

tableName.fieldName vs. tableName.[fieldName] vs. [tableName].[fieldName]

[Forms]![formName].[nbrCustAcct] vs. [Forms].[formName].[nbrCustAcct]

[Forms]![formName].[nbrCustAcct] vs. [formName].[nbrCustAcct]

Me.fieldName vs. formName.fieldName

And why am I supposed to use [ ]'s for field names but not table names in a DLookUp("[fieldName]","tableName", ...)

Cheers

 

[CJ_London]

Access will provide square brackets in queries whether they are required or not, but will get confused if there are spaces in the table and/or field names.

So these are both valid
[Table1].[Field1]
Table1.Field1

However if you have spaces or your name is a reserved word, you must use square brackets. It is better to avoid reserved words, even with square brackets Access can still get confused.

In a query, if the field name is unique across all tables in the query then you don't need to refer to the table name.

The ! refers to a control name, A '.' refers to a field name. Often these are the same when the control has the same name as the controlsource

Me refers to the form object and can be used to reference objects within the form (or report). You don't have to use Me but it helps with documentation or if there are other objects with the same name.

And why am I supposed to use [ ]'s for field names but not table names in a DLookUp("[fieldName]","tableName",

You can use them for table names, you don't need to use them for field names - unless the names contain spaces or in the case of a field you are doing a calculation - e.g. DLookUp("[fieldName]/[Fld2]","tableName",

 

[Brianwarnock]

If you search on bang and user pat Hartman you will get many hits, here is one although the main answer is actually by Bob Larson

http://www.access-programmers.co.uk/forums/showthread.php?t=120877&highlight=Bang


Brian

 

[thardyjackson]

Thanks folks.

 

[Pat Hartman]

The ! refers to a control name, A '.' refers to a field name

That is backwards. Controls are a property of a form/report and so they are referred to with the '.'.

 

[CJ_London]

apologies!.