SQL Syntax Error

Hi Everyone,

I'm creating a macro which uses a query to search a database using two variables to define the search. When I get to the line .Refresh BackgroundQuery:=False (Highlighted in code), I get a SQL Syntax error message. Here's my code, any ideas why this is happening?

Sub RefreshTable()
'
' RefreshTable Macro
'

'
Dim from As Date
Dim untill As Date

from = Range("C2").Value
untill = Range("D2").Value
    Sheets("Sheet1").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=AR System ODBC Data Source;ARServer=iu-kjggg8.co.uk;ARServerPort=6512;UID=pfrhh;;ARAuthentication=;ARU" _
        ), Array("seUnderscores=1;SERVER=NotTheServer")), Destination:=Range( _
        "Sheet1!$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT HPD_Help_Desk.Incident_Number, HPD_Help_Desk.Description, HPD_Help_Desk.First_Name, HPD_Help_Desk.Last_Name, HPD_Help_Desk.Priority, HPD_Help_Desk.Status, HPD_Help_Desk.SLM_Status, HPD_Help_Des" _
        , _
        "k.Company, HPD_Help_Desk.Last_Resolved_Date" & Chr(13) & "" & Chr(10) & "FROM HPD_Help_Desk HPD_Help_Desk" & Chr(13) & "" & Chr(10) & "WHERE (HPD_Help_Desk.Company='DCWR') AND (HPD_Help_Desk.Status>='Resolved') AND (HPD_Help_Desk.Last_Resolved_Date>{ts '20" _
        , _
        "from'} And HPD_Help_Desk.Last_Resolved_Date<{ts 'untill'})" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_ExternalData_1"
        .Refresh BackgroundQuery:=False        
      End With
    ActiveSheet.ListObjects("Table_ExternalData_1").TableStyle = ""
    Range("B10").Select
End Sub

Thanks for your help!

You are passing the literal string 'untill' in your SQL, not concatenating the variable into it.

Note: please use code tags whenever you post code as required by the forum rules. I have added them for you on this occasion but you must do it yourself in future, please.

Originally Posted by romperstomper

You are passing the literal string 'untill' in your SQL, not concatenating the variable into it.

So you're saying that currently I'm literally inserting the word "untill" into my query rather than the variable contained within "untill"? How do I refer to the variable then?


Thanks for the heads up on the code tags, I'll do that in the future

Correct. Instead of:

"from'} And HPD_Help_Desk.Last_Resolved_Date<{ts 'untill'})" _

you need:

"from'} And HPD_Help_Desk.Last_Resolved_Date<{ts '" & untill & "'})" _

Your From date check appears to have a random '20' on the front of it, which won't be helping. I also can't see the point of aliasing a table with its actual name. Try using:

.CommandText = "SELECT HPD_Help_Desk.Incident_Number, HPD_Help_Desk.Description, HPD_Help_Desk.First_Name, " & _
         "HPD_Help_Desk.Last_Name, HPD_Help_Desk.Priority, HPD_Help_Desk.Status, HPD_Help_Desk.SLM_Status, " & _
         "HPD_Help_Desk.Company, HPD_Help_Desk.Last_Resolved_Date FROM HPD_Help_Desk " & _
         "WHERE (HPD_Help_Desk.Company='DCWR') AND (HPD_Help_Desk.Status>='Resolved') " & _
         "AND (HPD_Help_Desk.Last_Resolved_Date>{ts '" & from & "'} " & _
         "And HPD_Help_Desk.Last_Resolved_Date<{ts '" & untill & "'})"

and see if that helps.

Maybe date format - try:

.CommandText = "SELECT HPD_Help_Desk.Incident_Number, HPD_Help_Desk.Description, HPD_Help_Desk.First_Name, " & _
         "HPD_Help_Desk.Last_Name, HPD_Help_Desk.Priority, HPD_Help_Desk.Status, HPD_Help_Desk.SLM_Status, " & _
         "HPD_Help_Desk.Company, HPD_Help_Desk.Last_Resolved_Date FROM HPD_Help_Desk " & _
         "WHERE (HPD_Help_Desk.Company='DCWR') AND (HPD_Help_Desk.Status>='Resolved') " & _
         "AND (HPD_Help_Desk.Last_Resolved_Date>{ts '" & Format(from, "yyyy-mm-dd") & "'} " & _
         "And HPD_Help_Desk.Last_Resolved_Date<{ts '" & Format(untill, "yyyy-mm-dd") & "'})"

Have you checked the values of From and Untill? Also, have you tried recording a macro of setting up this connection? (or is that what this is - if so, what was the original code?)

Should it be "d" instead of "ts" since there's no time?

>{d '" & Format(from, "yyyy-mm-dd") & "'} " & _

<{d '" & Format(untill, "yyyy-mm-dd") & "'})"

Based on that and Colin's post, you probably need:

.CommandText = "SELECT HPD_Help_Desk.Incident_Number, HPD_Help_Desk.Description, HPD_Help_Desk.First_Name, " & _
         "HPD_Help_Desk.Last_Name, HPD_Help_Desk.Priority, HPD_Help_Desk.Status, HPD_Help_Desk.SLM_Status, " & _
         "HPD_Help_Desk.Company, HPD_Help_Desk.Last_Resolved_Date FROM HPD_Help_Desk " & _
         "WHERE (HPD_Help_Desk.Company='DCWR') AND (HPD_Help_Desk.Status>='Resolved') " & _
         "AND (HPD_Help_Desk.Last_Resolved_Date>{ts '" & Format(from, "yyyy-mm-dd hh:mm:ss") & "'} " & _
         "And HPD_Help_Desk.Last_Resolved_Date<{ts '" & Format(untill, "yyyy-mm-dd" hh:mm:ss) & "'})"

Hi Jo,

One last, small detail. It doesn't matter in this case because the mm follows on after hh (so the Format function knows to use minutes instead of months), but I recommend that you get in the habit of using "n" for minutes.
eg.

Format(from, "yyyy-mm-dd hh:nn:ss")