1

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.

bad_coder
  • 11,289
  • 20
  • 44
  • 72
Bughater
  • 53
  • 3
  • 9

2 Answers2

1

The correct syntax should be:

[Table-1].[Field-1]

Or, using bang notation:

[Table-1]![Field-1]
Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • Maybe, but the Access doesn't accept it and automatically puts additional [ … ] around it — also when I choose this field from the fields' drop-down menu. And this leads to above-mentioned #Name?-error. – Bughater Nov 26 '18 at 16:56
  • @Bughater I can't reproduce this behaviour: creating a form with record source set to a query containing two instances of `Field-1` sourced from two tables `Table-1` and `Table-2`, I am able to set the control source of a text box to `[Table-1].[Field-1]` with no issue. Perhaps you could update your question with the record source for your form and a screenshot of where you are trying to set the control source? – Lee Mac Nov 26 '18 at 17:04
  • You're right for a new database with 2 simple tables each with 2 fields. However, my form has 30 fields and is based on a query with 8 tables, >30 fields and 8 calculated variables. There I can set the text box to [Table-1].[Field-1], too, but when I save the form's design view this changes immediately to [[Table-1].[Field-1]] and I get the error in the form. Same result when I just close the design view without expressly saving. However, this form otherwise works perfectly (with below work-around) and doesn't appear to be corrupt in any way. – Bughater Dec 01 '18 at 11:24
0

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.

Bughater
  • 53
  • 3
  • 9