Run Time Error 3075 - Syntax Error in Date?

Question

I am using Access 2007, and VBA to run a report to Excel. The SQL statement I used looks like this:

aSQL = "SELECT tblBasicDemographics.LastName, tblBasicDemographics.FirstName, tblClientAddresses.County, tbl_ChildAttendance.ClientNumber, tbl_ChildAttendance.AttDate, tbl_ChildAttendance.ChildProgram, tbl_ChildAttendance.Time1In, tbl_ChildAttendance.Time1Out, tbl_ChildAttendance.Time2In, tbl_ChildAttendance.Time2Out, tbl_ChildAttendance.Status FROM (tbl_ChildAttendance INNER JOIN tblBasicDemographics ON tbl_ChildAttendance.ClientNumber=tblBasicDemographics.ClientNumber) INNER JOIN tblClientAddresses ON tblBasicDemographics.ClientNumber=tblClientAddresses.ClientNumber WHERE (tbl_ChildAttendance.AttDate BETWEEN #FiscalStart# AND #FiscalEnd#)"


The FiscalStart and FiscalEnd are both text boxes in the form with a Short Date format (also I used an autopicker to select the date). For some reason everytime I try to run the report I get the error:

"Run-time error '3075':

Syntax error in date in query expression '(tbl_ChildAttendance.AttDate BETWEEN #FiscalStart# AND #FiscalEnd#'.

Any help would be greatly appreciated! Thank you in advance.

Answer 1

Your qry is reading the variables as dates - you need to build your SQL statement using this syntax:



aSQL = "SELECT tblBasicDemographics.LastName, tblBasicDemographics.FirstName, tblClientAddresses.County, tbl_ChildAttendance.ClientNumber, tbl_ChildAttendance.AttDate, tbl_ChildAttendance.ChildProgram, tbl_ChildAttendance.Time1In, tbl_ChildAttendance.Time1Out, tbl_ChildAttendance.Time2In, tbl_ChildAttendance.Time2Out, tbl_ChildAttendance.Status FROM (tbl_ChildAttendance INNER JOIN tblBasicDemographics ON tbl_ChildAttendance.ClientNumber=tblBasi… INNER JOIN tblClientAddresses ON tblBasicDemographics.ClientNumber=tblCli…

WHERE (tbl_ChildAttendance.AttDate BETWEEN #" & FiscalStart & "# AND #" & FiscalEnd & "#)"



PS-

make sure you do err checking on the date (leap year, 30 vs. 31 days, etc... and don't pass the time if it's on your textbox.



HTH

Answer 2

Run-time Error 3075

Answer 3

Runtime Error 3075

Answer 4

This Site Might Help You.



RE:

Run Time Error 3075 - Syntax Error in Date?

I am using Access 2007, and VBA to run a report to Excel. The SQL statement I used looks like this:



aSQL = "SELECT tblBasicDemographics.LastName, tblBasicDemographics.FirstName, tblClientAddresses.County, tbl_ChildAttendance.ClientNumber, tbl_ChildAttendance.AttDate,...