MS-Access 2010 Form: field doesn't accept data source with 2 hyphens

Question

I have a form based on a multiple-tables query. As some fields from different tables have the same names, I must add the corresponding table's name. However, there are hyphens in the tables' names as well as in the fields' names (both inherited from foreign Excel tables).

In VBA there is no problem: [Table-1.Field-1] always works well (also in SQL queries). However, when I write this in drafting mode as data source into the form, Access "thinks" this would be wrong and replaces it automatically with [[Table-1].[Field-1]] - with the result that the form then displays the error #Name?. I tried to replace [] by quotes but without any success.

Note that there is no error when only the table or only the field has a hyphen: both MyTable.[Field-1] and [Table-1].Myfield are accepted by the form.

Answer 1

The correct syntax should be:

[Table-1].[Field-1]

Or, using bang notation:

[Table-1]![Field-1]

Answer 2

Meanwhile I found not a true answer, but nevertheless a quite satisfactory workaround by adding following calculated field into the query:

MyWorkAround: [Table-1.Field-1]

Then I can simply refer to [MyWorkAround] in the corresponding form's field to avoid the form's bug. But this isn't really very elegant !

Note that I always use [ … ] around fields, even where not necessary. This practice helps avoiding a lot of errors.