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

Thanks I'm a little new to this not sure what you mean by static. I tried to open the access library file you posted, but get an error message that I can't open access file from a previous version.

My report is just adding how many good shows vs bad shows a supervisor reports in a period. Columns Spv Name, Good counted, Bad Counted, Total. Report Header would show the Date range information was taken.

Thanks Again

Answer 2

Thanks Scott, I am a little new to this so I going to have study on how to create dynamic headings in a report for crosstalk query. Though it would be simpler. 

My report is just adding how many good shows vs bad shows a supervisor reports in a period. Columns Spv Name, Good counted, Bad Counted, Total. Report Header would show the Date range information was taken.

Thanks Again

Answer 3

As Duane has said, you can successfully use a Crosstab as the Recordsource of a report using one of two methods. The first works great if you have a static list of column headings. For example, reports that show the Months of the year. By setting the Column Headings property to:

Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

I always have 12 columns. 

The other method is what I referred to as dynamic headings. And is detailed in the link Duane gave you. Basically, you create columns for as much as you can fit and name them generically. Then use code to populate the controls.

Answer 4

I have successfully used crosstabs as the record source for reports many times. If the possible values of [Good Show/ Bad Show] are static then there shouldn't be an issue. If they are not static then another solution will be required. I recommend the solution found at Rogers Access Library.

Answer 5

I expect there is at least one record with no value in the field [Good Show/ Bad Show]. Since your crosstab query doesn't have anything in the Column Headings property, this record generates a column with no column heading which causes your error.

If the possible values for [Good Show/ Bad Show] are limited and won't change, I would add all possible values to the Column Headings property.