Help with a Data Query!

[ravikiran]

Hello Gurus,




I need a small help with amending a data query to VBA. I want to customize the dates in the following Data Query with variables

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=******;Description=ITF sap data;UID=*******;;APP=2007 Microsoft Office system;WSID=CNU14608JX-W7;DATABASE=Runtime;Network=DB" _
        ), Array("MSSOCN;Address=***-mdadb001,1833")), Destination:=Range("$A$1")). _
        QueryTable
        .CommandText = Array( _
        "SELECT ITFSAP.LineNum, ITFSAP.SAP, ITFSAP.SAPDesc, ITFSAP.StartDate, ITFSAP.LOTNo, ITFSAP.PONo, ITFSAP.EntryDate, ITFSAP.PlannedQty" & Chr(13) & "" & Chr(10) & "FROM Runtime.dbo.ITFSAP ITFSAP" & Chr(13) & "" & Chr(10) & "WHERE (ITFSAP.SAPDesc Not Like '%Te" _
        , _
        "st%') AND (ITFSAP.StartDate>={ts '2012-10-01 00:00:00'} And ITFSAP.StartDate<={ts '2012-10-29 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY ITFSAP.StartDate" _
        )
        .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_Query_from_itfsap"
        .Refresh BackgroundQuery:=False
    End With

I want to refer the start date to be greater than the variable "StartDt" and less than current date


I am a newbie with SQL queries. Help will be greatly appreciated.


Thank you,


Regards,
Ravi.

PS: This is a duplicate post. I tried on another excel forum, where I didn't attract any attention. So I am seeking help here. Thank you!

[pbaldy]

Try

"st%') AND (ITFSAP.StartDate>={ts '" & StartDt & "'} And ITFSAP.StartDate<={ts '" & Date() & "'})" & Chr(13) & "" & Chr(10) & "ORDER BY ITFSAP.StartDate"

I'm not familiar with some of what you have, but that should concatenate the respective values into the string you had. By the way, the Chr() functions are unnecessary; the database engine will ignore them anyway.

[ravikiran]

Thank you Baldy, that worked perfectly well.

Yeah, I am about to ask my second query about the Char() functions. Sure I removed them.

Regards,
Ravi.

[pbaldy]

Happy to help.