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, Jun 15 '16 at 12:24
You have two WHERE sections; is the issue in the first, second, or both? — Dave, Jun 15 '16 at 12:26
@LiamH Yes, I have a public function with all of the necessary items in it, but if you can think of another way to have the where clause get the information over the last 30 days, I'd be open to suggestions. — Probable, Jun 15 '16 at 12:29
Can you post the public function where the values are declared and set? — Dave, Jun 15 '16 at 12:39
[How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) -- 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, Jun 15 '16 at 12:41
And having variables `@DailyReportEndDate` embedded in the SQL string won't work in Access. You'll need string concatenation. — Andre, Jun 15 '16 at 12:43
@Andre I use this query in another section of my program and it works with the DailyReportEndDate there. — Probable, Jun 15 '16 at 12:47
To help you, we need the *actual* SQL SELECT statement that is run. After all modifications / replacements. — Andre, Jun 15 '16 at 12:49
Just did it, so it looks like the replacements aren't happening. Here is what comes out of the immediate window: — Probable, Jun 15 '16 at 12:53
SELECT 'Completed' AS Status, Count(tblPMWOs.PMWOID) AS CountOfPMWOID From tblPMWOs WHERE (([tblPMWOs].[DateComplete] >= DateAdd('m',-10,DateValue(#[@DailyReportStartDate]#))) AND ([tblPMWOs].[DateComplete] < DateAdd('d',1,DateValue(#[@DailyReportEndDate]#)))) Group By 'Completed'UNION ALL SELECT 'Open' AS Status, Count(tblPMWOs.PMWOID) AS CountOfPMWOID From tblPMWOs WHERE (((tblPMWOs.DateGenerated) < #[@DailyReportEndDate]#) And ((tblPMWOs.DateComplete) >= #[@DailyReportEndDate]# Or (tblPMWOs.DateComplete) Is Null)) Group BY 'Open' It is the same. — Probable, Jun 15 '16 at 12:54
And there is your problem (one of them). I also don't see a connection between your dynamic SELECT string and the function. The function replaces the '@' variables in existing queries, but not in a dynamic SQL string. — Andre, Jun 15 '16 at 13:03
I know the issue now, I am not prompting for the variables in the current area I'm using this in. Do you have any insight as to obtaining the information I'm trying to get over he last 30 days. Date query's are my biggest issue. — Probable, Jun 15 '16 at 13:12

score 1 · Answer 1 · answered Jun 15 '16 at 13:00

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 '16 at 13:00

Gustav

53,498
7
29
55

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. – Probable Jun 15 '16 at 13:06
Then you must back to basics: _Select * From Table_ – Gustav Jun 15 '16 at 14:50

Error: "Syntax error in date in query expression"

1 Answers1