Query failing

[woodyinoz]

Hi all,

I've got the following query running in Excel.

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=SQLSvr;Description=Connection to SQL Server (SQLSVR);UID=sa;PWD=password;APP=Microsoft Office 2003;WSID=SWOODWARD;DATABASE=FMDB;Networ" _
        ), Array("k=DBMSSOCN")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT GRNHeader2a.VENDOR_ID, GRNHeader2a.CURRENCY_ID, GRNHeader2a.ID, GRNHeader2a.RECEIVED_DATE, GRNHeader2a.BUY_RATE" & Chr(13) & "" & Chr(10) & "FROM FMDB.dbo.GRNHeader2a GRNHeader2a" & Chr(13) & "" & Chr(10) & "WHERE (GRNHeader2a.INVOICE_ID Is Null) AN" _
        , _
        "D (GRNHeader2a.CREATE_DATE Between {ts '" & Format(StartDate, "yyyy-mm-dd hh:nn:ss") & "'} And {ts '" & Format(EndDate, "yyyy-mm-dd hh:nn:ss") & "'})" _
        )
        .Name = "Query from SQLSvr"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

However, it fails and highlights the line,

.Refresh BackgroundQuery:=False

This has only started happening since I replaced hard coded dates in the query with the format date. I did this as I want the user to enter the dates.

Can anyone give me a hand with solving this? I've tried a few things but to no avail!

Thanks in advance,

Woody

 

[PHV]

You may try something like this:
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=SQLSvr;Description=Connection to SQL Server (SQLSVR);UID=sa;PWD=password;APP=Microsoft Office 2003;WSID=SWOODWARD;DATABASE=FMDB;Network=DBMSSOCN" _
, Destination:=Range("A1"))
.CommandText = _
"SELECT VENDOR_ID, CURRENCY_ID, ID, RECEIVED_DATE, BUY_RATE" _
& " FROM FMDB.dbo.GRNHeader2a" _
& " WHERE INVOICE_ID Is Null AND (CREATE_DATE Between '" & Format(StartDate, "yyyy-mm-dd") & " 00:00:00' And '" & Format(EndDate, "yyyy-mm-dd") & " 23:59:59')"
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[woodyinoz]

Excellent, thats PH that was great.

One more question......

How do I pass the value of startdate and enddate onto other queries which live in different modules on the same spreadsheet?

Thanks for your help,

Woody

 

[xlbo]

make them PUBLIC variables

 

[woodyinoz]

Ok,

I've setup a module and declared my two variables.
How do I now use those variables in a query similar to the one at the start of this post?

Thanks,

Woody

 

[xlbo]

If they are declared as public, they can be used in the same way as normal variables - all that changes is they can be used in any module rather than the one they are declared in

 

[woodyinoz]

Sorry,

What I meant was how to I reference a variable in a query? I've never used a variable within a query before.... Well only in the example in this post!

Woody

 

[xlbo]

Well only in the example in this post!

.............in exactly the same way

 

[woodyinoz]

Sorry xbo... I'm possibly the most impatient stupid person on this planet... Instead of trying things I presume there is some black art!

thanks for your help,

Woody

 

[SkipVought]

Clarification???

xlbo has suggested that in the same way that you modified the .CommandText string, using the Format function with your StartDate and EndDate variables, you could modify any other .CommandText string using suitable variables.

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[xlbo]

no worries Woody and thx for providing the clarification Skip