Error: "Syntax error in date in query expression"

Question

I am using MS Access to make a program, and I used this query previously and it worked, but it was in a completely different situation. I'm not sure how to modify it to make it work here. I need to select and count the number of open Workorders over the last 30 days, but there is an issue with my date in the WHERE section.

Here is the query:

"SELECT 'Completed' AS Status, Count(tblWorkorders.WOID) AS CountOfWOID " & _
        "From tblWorkorders " & _
        "WHERE ((tblWorkorders.CompleteDate >= DateAdd('m',-10,DateValue(#[@DailyReportStartDate]#))) And (tblWorkorders.CompleteDate < DateAdd('d',1,DateValue(#[@DailyReportEndDate]#)))) " & _
        "UNION ALL " & _
        "SELECT 'Open' AS Status, Count(tblWorkorders.WOID) AS CountOfWOID " & _
        "From tblWorkorders " & _
        "WHERE (((tblWorkorders.RequestDate) < #[@DailyReportEndDate]#) And ((tblWorkorders.CompleteDate) >= #[@DailyReportEndDate]# Or (tblWorkorders.CompleteDate) Is Null) AND ((tblWorkorders.StatusID) In (1,4))) " & _
        "Group BY 'Open' "

EDIT: Here is the public function:

Public Function CreateDailyReport() As Boolean
Dim reportName As String
Dim QueryName As String
Dim q As String


q = "SELECT * FROM tblDowntime WHERE 1=0"
reportName = "rptDailyReport"
QueryName = "qryDailyReport"
Modify_QuerySQL QueryName, q


Modify_QuerySQL "qryDailyReport_Downtime", Replace(Replace(SQL_DailyReportDowntime, "[@DailyReportStartDate]", DailyReportStartDate), "[@DailyReportEndDate]", DailyReportEndDate)
Modify_QuerySQL "qryDailyReport_GeneralNotes", Replace(Replace(SQL_DailyReportGeneralNotes, "[@DailyReportStartDate]", DailyReportStartDate), "[@DailyReportEndDate]", DailyReportEndDate)
Modify_QuerySQL "qryDailyReport_PM", Replace(Replace(SQL_DailyReportPM, "[@DailyReportStartDate]", DailyReportStartDate), "[@DailyReportEndDate]", DailyReportEndDate)
Modify_QuerySQL "qryDailyReport_WorkOrder", Replace(Replace(SQL_DailyReportWorkOrder, "[@DailyReportStartDate]", DailyReportStartDate), "[@DailyReportEndDate]", DailyReportEndDate)

DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, Get_TempPath & reportName & ".pdf", True


End Function

Are [@DailyreportStartDate] and [@DailyReportEndDate] now fields in your form? — LiamH
– LiamH, Commented Jun 15, 2016 at 12:24
You have two WHERE sections; is the issue in the first, second, or both? — Dave
– Dave, Commented Jun 15, 2016 at 12:26
How to debug dynamic SQL in VBA -- Please add a formatted output of a Debug.Print of your SQL string to your question, so that we see the actual SQL that will be run. — Andre
– Andre, Commented Jun 15, 2016 at 12:41

Gustav · Accepted Answer · 2016-06-15 13:00:32Z

1

You feed non-declared/assigned variables DailyReportStartDate and DailyReportEndDate here:

Modify_QuerySQL "qryDailyReport_Downtime", Replace(Replace(SQL_DailyReportDowntime, "[@DailyReportStartDate]", DailyReportStartDate), "[@DailyReportEndDate]", DailyReportEndDate)

Also this makes little sense:

DateValue(#[@DailyReportEndDate]#)

which - if DailyReportEndDate is a string - results in:

DateValue(#2016/06/15#)

which simply should be:

#2016/06/15#

or - if DailyReportEndDate is a date - is casted to a string of a format out of your control like:

DateValue(#15-06-2016#)

which should have been:

#2016/06/15#

answered Jun 15, 2016 at 13:00

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Probable Over a year ago

Okay, I understand what you are saying. I am not prompting for the variables in this area. I can't seem to get any query to work to obtain the information from my database for the last 30 days.

Gustav Over a year ago

Then you must back to basics: Select * From Table

Collectives™ on Stack Overflow

Error: "Syntax error in date in query expression"

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related