Query Problem - can't use field with ampersand (&) in its name

[VC]

I have linked to an older table to which I need to add new records. My
problem is that one of the fields is named S&H (honest!), and
Access won't run the query if this field is in a query column. I do not have
the authorization to change the field name.

How can I operate on this field?

 

[VC]

I should have said I am using Access 2002/XL and the database table in
question appears to be Access 2000.

 

[Douglas J. Steele]

Try putting square brackets around the field name (and Alias it to something
without the special characters.

You might have to go into the SQL View (under the View menu) in order to do
this. You'll want to change

SELECT TableName.FieldName, TableName.S&H, TableName, FieldName

to

SELECT TableName.FieldName, TableName.[S&H] AS SH, TableName, FieldName

 

[Rob Parker]

Seems odd.

I just set up a test, using an A2002 format database, with a linked table
from an A2000 format database; I can quite happily set up and run a query
using a field named S&H. The only proviso is that, if I design the query in
SQL view rather than in the query design grid, I must enclose the fieldname
in square brackets, thus:
SELECT [S&H] FROM MyLinkedTable;

Designing in the query design grid causes the square brackets to be inserted
in the SQL automatically.

If this doesn't help, please post the SQL of the query which doesn't run.

Rob