1

I am having following VBA Code that has been giving a syntax error. Can someone please help me figure out what is causing the error?

Private Sub Command11_Click()
    Dim EndingDate As Date
    'Getting ending date from Label named endDate
    EndingDate = endDate
    StartingDateTxt = DateSerial(Year(EndingDate), Month(EndingDate) - 15, Day(EndingDate))

    Dim customerRecords As New ADODB.Recordset
    customerRecords.Open "SELECT COUNT(*) AS N FROM (SELECT   DISTINCT E.Date,"&_
                        "E.[Inv Num],   E.CusName, E.[Name Street1], E.[Name Street2], "&_
                        "E.[Name City], E.[Name State], E.[Name Zip], E.[Account #], E.Amount FROM TempFromExcel "&_
                        "AS E  INNER JOIN TempFromExcel AS X ON E.CusName = X.CusName "&_
                        "WHERE (((DateDiff("d",X.Date,E.Date))>=30))  AND E.Date >= '" & StartingDateTxt & "' and"&_
                        "E.Date <= '" & endDate & "') AS T ;", _
                 CodeProject.Connection , _
                 adOpenStatic, _
                 adLockOptimistic, _
                 adCmdText
    MsgBox customerRecords("N")

End Sub

My Query is taking both dates and finding the results that are between the two dates.

I think I may be missing at that part only. The rest seems fine as I had explicitly check the query and it runs fine. So is this right ?

E.Date >= '" & StartingDateTxt & "' and E.Date <= '" & endDate & "'

This has been corrected, in the answer but still am getting syntax error in Select statement first line. Am missing something?

Newd
  • 2,174
  • 2
  • 17
  • 31
ms8
  • 417
  • 2
  • 13

2 Answers2

1

In Microsoft Access SQL query you have to encapsulate Date value into ##, like for example, #06/01/2015#. Pertinent to your case it should look like:

E.Date >= #" & StartingDateTxt & "# AND E.Date <=#" & endDate & "#"

Hope this may help.

Alexander Bell
  • 7,842
  • 3
  • 26
  • 42
  • Can you please rewrite the query then ? – ms8 Jun 01 '15 at 20:05
  • Also its just a guess, Is other part of Select query written in right way ? – ms8 Jun 01 '15 at 20:07
  • I have extended the answer as per your request addressing your original question. If you are satisfied, then please mark it accepted. If you have more question(s), then please post it separately. Thanks and regards, – Alexander Bell Jun 01 '15 at 20:07
  • Now error is in first line according to debugger that is customerRecords.Open "SELECT COUNT(*) AS N FROM (SELECT DISTINCT E.Date,"&_ – ms8 Jun 01 '15 at 20:15
  • As I have advised you already, please post your second question separately highlighting just a problematic SQL part (remote debugging of your code is a difficult task without seen the actual Table structure and sample data). Best regards, – Alexander Bell Jun 01 '15 at 20:18
0

Try changing these lines:

"WHERE (DateDiff('d', X.Date, E.Date) >= 30 AND E.Date >= #" & Format(StartingDateTxt, "yyyy\/mm\/dd") & "# and " & _
"E.Date <= #" & Format(endDate, "yyyy\/mm\/dd") & "#) AS T ;", _
Gustav
  • 53,498
  • 7
  • 29
  • 55