Access:” does not recognized as a valid field name or expression.

Published on:
Last updated:

This post is also available in: Japanese

I got an error using Access query "The Microsoft Access database engine does not recognize '' as a valid field name or expression.", so write a memo as a solution.

In this case, an error occurred while attempting to run query based on crosstab query as the original data.

From the conclusion, the cause of the error was not displayed in the column header in the crosstab query(there is no data in the table that you want to accumulate in the crosstab query).

In other words, column headings in the crosstab query show any items to aggregate.
This is not a cause for being null, so it is not possible to avoid using the Nz() function.

As a workaround, Right-click the "Column Heading" item in the crosstab query to display the properties, and display the fixed column in the "Query Column Heading" field .
If you do so, a null value will be entered even if there is no data in the item to be aggregated, so you can avoid the Nz() function and execute the query.

About
Kuniyoshi Takemoto is the founder of Amelt.net LLC, and editor of this blog(www.amelt.net).Learn more and follow me on LinkedIn.