MS Access database engine does not recognize..... as valid name or expression

Question

Error #3070 MS Access database engine does not recognize " as valid name or expression

I am using a form to enter criteria into a query using select and crosstab query to generate a report.  Parameters are defined in all queries used, date formats are consistent throughout.  When I enter a date using a single digit month such as 07/01/2016 as start date, and 07/31/16 as end date, queries and report run with no problem.....  resulting data is correct.  If I use a date with a two digit month such as 11/01/2016 to 11/30/2016, I get the error message and all stops.  It is only the months of 10, 11, and 12 that the query will not read.  

PARAMETERS [Forms]![TypeRptCriteria]![Start] DateTime, [Forms]![TypeRptCriteria]![End] DateTime;

SELECT [CATS Stats].ID, [CATS Stats].[Type of Cancer], Gender1_Crosstab.[Family Member], [CATS Stats].Age, [CATS Stats].Gender, Gender1_Crosstab.Female, Gender1_Crosstab.Male, [CATS Stats].Ethnicity, [CATS Stats].[Zip Code], [CATS Stats].[Referral Date], [CATS Stats].[Referral Source], [CATS Stats].[Start Date], [CATS Stats].[End Date], [CATS Stats].[Sessions Offered], [CATS Stats].Referral, [CATS Stats].[Pre Score], [CATS Stats].[Post Score], Year([Start Date]) AS [Year]

FROM [CATS Stats] LEFT JOIN Gender1_Crosstab ON ([CATS Stats].[Family Member] = Gender1_Crosstab.[Family Member]) AND ([CATS Stats].[Type of Cancer] = Gender1_Crosstab.[Type of Cancer])

WHERE ((([CATS Stats].[Start Date]) Between [Forms]![TypeRptCriteria]![Start] And [Forms]![TypeRptCriteria]![End])) OR ((([Forms]![TypeRptCriteria]![Start]) Is Null) AND (([Forms]![TypeRptCriteria]![End]) Is Null));

Any possible solutions would be greatly appreciated!

RonMNJ

Answer 1

I wonder if the problems are due to missing columns in the crosstab due to data that is missing in the selected date range.  Are you using the crosstab query's Column Headings property to specify all the columns that are to appear in the output?

Answer 2

Thank you!!   It was in fact that I had not set the ColumnHeadings property in the Crosstab Query and an empty string was hanging out there.  Correction made, all runs well.

Again, thank you!

Answer 3

.... Even more bizarre....  I am working with 95 records, providing query criteria for the report by form.  The age(s) in the table range between 2 and 14 YOA.  If I use age 7, 9, or 14, the report will run perfectly. Using any of the other ages as restriction causes the previous error message to appear and the report does not run.  Totally confused.  I have used forms to specify criteria in reports/queries many times and have never had this occur before.  Only difference I can think of is this is the first database I'm working on since my computer operating system was upgraded to Windows 10.  I am using Microsoft Office Professional 2010 version of Access.  Compatibility issue??

Dirk, thanks much for your response.

Answer 4

Yes....   only a single quotation shows in the error message, it does not specify what is not recognized. 

Yes...  the crosstab query has the same parameters set as the select query with which it is paired and it does open and run on its own.  

The strange thing is I added more records to the database table for the months of Oct, Nov, and Dec.  and the report will now run for those months.  Previously, I had only 3 records associated with each month.  I have added an additional criteria to one of the reports (age), and am experiencing the same issue again.

Answer 5

Are you saying that you get the error message with an empty string ('') for the name that the database engine doesn't recognize?  It doesn't tell you what it doesn't recognize?

Does your crosstab query, Gender1_Crosstab, also refer to the parameter form?  Can you open it successfully all by itself?