ACCESS 2013, The Microsoft database engine does not recognize " as a valid field name or expression

Question

I have a report that is based on a crosstab query that counts the amount of records a person has entered.  The parameter are base on a form control [Start Date] and [End Date]. The report works as long as I don't have a start date  = or greater than 9/20. The query will work no matter what dates are entered, but the report wont open if start date is 9/20 or greater, I get the error message that the database engine does not recognize " as a valid field name of expression. The database was created in 2007 and updated to 2013, this is when I started having the problem. Help does anyone no the answer to my problem.

Answer 1

You need to design your report to expect a varied number of columns since that is what a crosstab query returns.  You can't define a report based on the columns returned by your initial query trial.  The results will change as your data changes.

Trying to output a crosstab query to a report is probably not a good idea to begin with, because your columns will most likely exceed the paper size.  If you need to output your results for others, you might find that it's easiest to export your query results to an Excel file.

Answer 2

I suspect the problem is in data entered after that date. But as Duane says we need to see the SQL behind your recordsource. Also, is your report set up for dynamic headings?

I'm not sure what is meant by dynamic headings, but here is the SQL

Query Parameters dialog box

PARAMETERS [Forms]![TeamFilter]![Start Date] DateTime, [Forms]![TeamFilter]![End Date] DateTime;

TRANSFORM Count([Performance Window Extended].[ID ENTRY]) AS [CountOfID ENTRY]

SELECT [SPV Names].SPVNameRank, Count([Performance Window Extended].[ID ENTRY]) AS [Total Of ID ENTRY]

FROM [SPV Names] INNER JOIN [Performance Window Extended] ON [SPV Names].ID = [Performance Window Extended].[Reporting SPV]

WHERE ((([Performance Window Extended].[PW Date]) Between [Forms]![TeamFilter]![Start Date] And [Forms]![TeamFilter]![End Date]))

GROUP BY [SPV Names].SPVNameRank

PIVOT [Performance Window Extended].[Good Show/ Bad Show];

Thanks for helping

Answer 3

Here is the SQL

Query Parameters dialog box

PARAMETERS [Forms]![TeamFilter]![Start Date] DateTime, [Forms]![TeamFilter]![End Date] DateTime;

TRANSFORM Count([Performance Window Extended].[ID ENTRY]) AS [CountOfID ENTRY]

SELECT [SPV Names].SPVNameRank, Count([Performance Window Extended].[ID ENTRY]) AS [Total Of ID ENTRY]

FROM [SPV Names] INNER JOIN [Performance Window Extended] ON [SPV Names].ID = [Performance Window Extended].[Reporting SPV]

WHERE ((([Performance Window Extended].[PW Date]) Between [Forms]![TeamFilter]![Start Date] And [Forms]![TeamFilter]![End Date]))

GROUP BY [SPV Names].SPVNameRank

PIVOT [Performance Window Extended].[Good Show/ Bad Show];

Thanks for helping.

Answer 4

I suspect the problem is in data entered after that date. But as Duane says we need to see the SQL behind your recordsource. Also, is your report set up for dynamic headings?

Answer 5

Please share the SQL view of the crosstab. Did you declare the data types of the parameters? Did you enter column headings in the Column Headings property?

Are you using dates as column headings or possibly persons?