SQL Error / VBA generated ODBC query

[bobwilko]

Hi Everyone,

I am having no success in creating a query that accesses a Sage Line 50 Database. I have to generate a query on the fly which takes a starting date from a cell in the orders sheet. When I hard code the date as in the first block, the query works fine. When I try to use the DT variable as in the second block of code, it throws up an SQL error. IF I go in to the MS Query and examine the query and then return the data to excel, it works perfectly and returns the required data to the excel sheet. In the MS Query criteria it shows up as >=#21/03/2014# and as I say if I ask the query to return the data to excel it works as it should. But it refuses to run as a VBA subroutine.

Unfortunately, I cannot just leave the query in place and issue a refresh command with a parameter query as that crashes excel, so the only work around I could think of was to delete the query each time and rewrite it through VBA.

Does anyone have any ideas as to what is wrong with my code. I have tried defining DT as a Date, as a string, but nothing works.


Bob

Sub ResetQuery()

    Dim DT As Date
   ' HAve also tried as String    
    
    Sheets("Orders").Select
    DT = Range("A1").Value
    
    
    Range("A4").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.ListObject.QueryTable.Delete
    Selection.ClearContents
    Range("A4").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=Sage 2014;UID=user;PWD=pwd;", Destination:=Range("$A$4")). _
        QueryTable

     '   This block works with a hard coded date    
     '   .CommandText = Array( _
        "SELECT SALES_ORDER.ORDER_NUMBER, SALES_ORDER.ORDER_DATE, SALES_ORDER.NAME, SALES_ORDER.CUST_ORDER_NUMBER, SALES_ORDER.GLOBAL_DETAILS, SOP_ITEM.ITEM_NUMBER, SOP_ITEM.QTY_ORDER, SOP_ITEM.DESCRIPTION, SA" _
        , _
        "LES_ORDER.DESPATCH_STATUS_CODE, SALES_ORDER.CARR_NET, SALES_ORDER.CONSIGNMENT, SALES_ORDER.NOTES_1, SALES_ORDER.NOTES_2, SALES_ORDER.DESPATCH_DATE, SALES_ORDER.INVOICE_NUMBER, SOP_ITEM.DELIVERY_DATE, " _
        , _
        "SALES_ORDER.CUST_TEL_NUMBER, SALES_ORDER.DEL_NAME, SOP_ITEM.QTY_DELIVERED" & Chr(13) & "" & Chr(10) & "FROM SALES_ORDER SALES_ORDER, SOP_ITEM SOP_ITEM" & Chr(13) & "" & Chr(10) & "WHERE SOP_ITEM.ORDER_NUMBER = SALES_ORDER.ORDER_NUMBER AND ((SALES_ORDER.DES" _
        , "PATCH_STATUS_CODE<>2) OR (SALES_ORDER.ORDER_DATE>={d '2014-03-20'}))")
        

        ' This block does not work and throws ups SQL error
        .CommandText = Array( _
        "SELECT SALES_ORDER.ORDER_NUMBER, SALES_ORDER.ORDER_DATE, SALES_ORDER.NAME, SALES_ORDER.CUST_ORDER_NUMBER, SALES_ORDER.GLOBAL_DETAILS, SOP_ITEM.ITEM_NUMBER, SOP_ITEM.QTY_ORDER, SOP_ITEM.DESCRIPTION, SA" _
        , _
        "LES_ORDER.DESPATCH_STATUS_CODE, SALES_ORDER.CARR_NET, SALES_ORDER.CONSIGNMENT, SALES_ORDER.NOTES_1, SALES_ORDER.NOTES_2, SALES_ORDER.DESPATCH_DATE, SALES_ORDER.INVOICE_NUMBER, SOP_ITEM.DELIVERY_DATE, " _
        , _
        "SALES_ORDER.CUST_TEL_NUMBER, SALES_ORDER.DEL_NAME, SOP_ITEM.QTY_DELIVERED" & Chr(13) & "" & Chr(10) & "FROM SALES_ORDER SALES_ORDER, SOP_ITEM SOP_ITEM" & Chr(13) & "" & Chr(10) & "WHERE SOP_ITEM.ORDER_NUMBER = SALES_ORDER.ORDER_NUMBER AND ((SALES_ORDER.DES" _
        , "PATCH_STATUS_CODE<>2) OR (SALES_ORDER.ORDER_DATE>=" & Format(DT, "yyyy-mm-dd") & "))")
        
        
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
      '  .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Production"
        .Refresh BackgroundQuery:=False
    End With
    Range("A5").Select
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

Shouldn't it be?

SALES_ORDER.ORDER_DATE>={d " & Format(DT, "yyyy-mm-dd") & "'}))")

 

[ukmikeb]

Hi

Welcome to the MrExcel Forum.

Try replacing :-

(SALES_ORDER.ORDER_DATE>=" & Format(DT, "yyyy-mm-dd") & "))"

with :-

(dateserial(Year(SALES_ORDER.ORDER_DATE),Month(SALES_ORDER.ORDER_DATE),Day(SALES_ORDER.ORDER_DATE))>=" & DT  & "))"

and I would suggest abbreviating SALES_ORDER to ORD and SOP_ITEM to ITM or reducing it to two characters to make the SQL string more readable and manageable.

hth

 

[bobwilko]

Hi Andrew,

Many thanks for your quick response. Your suggestion threw up an SQL error, but when i looked hard, I could see there was a ' missing after the d

(SALES_ORDER.ORDER_DATE>=[COLOR=#ff0000]{d '[/COLOR]" & Format(DT, "yyyy-mm-dd") & "[COLOR=#ff0000]'}[/COLOR]))")

Once I put that in, the routine worked. Thank you so much for your help - I have spent so much time on this!

ukmikeb - thanks for your suggestions as well. Once I had Andrew's suggestions working I didn't try yours, but thanks anyway.


Bob