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

Again, you need to update the column headings property of your crosstab query. I'm expecting there is at least one record with nothing in the [Good Show/ Bad Show] field. Try this SQL:

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] IN ("Good Show","Bad Show");

Answer 2

What are all of the possible unique values of [Good Show/ Bad Show]?

By "Static" I mean they don't change over time like the list of months is static and the days of the week is static. SPVNameRank might not be static if there will be new values added or some removed.

Ok, I understand now Good Show or Bad Show are the only values they aren't any other choices. Names will change depending on if person has input data during the date range selected.

Answer 3

Ok, so you just have 2 calculated columns: Good Counter and Bad Counted. So all you need to do is add those the column Headings property. Then your crosstab will always have 4 columns even if there are no good or bads for supervisors.

Answer 4

What are all of the possible unique values of [Good Show/ Bad Show]?

By "Static" I mean they don't change over time like the list of months is static and the days of the week is static. SPVNameRank might not be static if there will be new values added or some removed.