Access 2007 syntax error

[Effie]

I recently upgraded to Access 2007 and I'm having trouble with one type of
query from my database that I designed in Access 2003. Its a simple Access
query using the query design window--I don't know how to program SQL. I get
the following error message when I try to run the query:
Syntax error in query expression '(((Parameters.[Major Parameter
Category])="Nitrate"))'.

The backbone of the database is 4 tables: Sites, Samples, Chemistry Data,
Parameters. "Major Parameter Category" is a field in the Parameters table,
and I'm trying to return only the data in the "Nitrate" category. Sites has
a primary key "Site ID" and links to Samples with a one-to-many relationship.
Samples has a primary key "Sample ID." Parameters has a primary key
"Parameter ID." Chemistry Data has both of these IDs (Sample ID and Parameter
ID) to define its primary key, and Chemistry Data links to both of these
tables (Samples and Parameters).

The query lists fields from each of the four tables, but its only the
Parameters table that I want to use to specify criteria. I get the above
type of error message whenever I put in criteria for any field from the
Parameters table. If I take the criteria out of the Parameters fields and
put criteria in the fields from any of the other 3 tables, I have no problem.
Help? This query used to work A-OK in Access 2003.

 

[Jerry Whittle]

I believe that you've hit on a reserved word problem. There are certain words
reserved for SQL that you shouldn't use in table or field names such as
Parameter or Parameters. Looks like A07 has tightened up on its checking for
these words.

One trick is to slightly change the table or field name from something like
Parameters to tblParameters. Of course this would break all the queries,
forms, and reports based on that table.

You could also try putting square brackets around the table name like so:

([Parameters].[Major Parameter Category])

For a full list of reserved words to avoid, check out the following:

http://support.microsoft.com/kb/286335/