Query For Excel Macro

[kalragarvita]

Hi Guys!
I am writing a macro which fetched data from SQL Sever according to specified date range.Using query as below

"SELECT ""HPD:HelpDesk" ".""Assigne d To Group+"", ""HPD:HelpDesk" ".""Assigne d To Individual+"", ""HPD:HelpDesk" ".""Assigne e Login Name"", ""HPD:HelpDesk" ".""Case Type"", ""HPD:HelpDesk" ".""Case ID+""" & Chr(13) & "" & Chr(10) & "FROM ""HPD:HelpDesk" " """ _
, _
"HPD:HelpDesk"" " & Chr(13) & "" & Chr(10) & "WHERE(""HPD:He lpDesk"".""Case Type""='Inciden t') AND ((" HPD:HelpDesk"." Arrival Time">={ts '2008-09-15 00:00:00'} And "HPD:HelpDesk". "Arrival Time"<={ts '2008-09-22 00:00:00'}"))"_
)

Now when trying to generalize this and inspite of passing date, Taking this from Input Box it is not woring giving as Runtime error 1004.

Please Help!

[ck9663]

Your run time error seems to be an excel issue, not SQL Server. In any case, try to get the entire query string and paste it to SQL Analyzer, then execute it, see what happens.

-- CK

[kalragarvita]

Your run time error seems to be an excel issue, not SQL Server. In any case, try to get the entire query string and paste it to SQL Analyzer, then execute it, see what happens.

-- CK

Thanks CK,
This is my worry i can not have direct access to the questioned server and can not run this query as usual! A twist..
I think the issue is with date variable.When I did hardcode with date "2008-09-10 00:00:00 it is working also when i give this variable type string it is working but as obvious we can not use date range for string type!
Now when trying this with Date type it is giving error.

Latest query:
"SELECT ""HPD:HelpDesk" ".""Arrival Time"", ""HPD:HelpDesk" ".""Assigne d To Group+"", ""HPD:HelpDesk" ".""Assigne d To Individual+"", ""HPD:HelpDesk" ".""Case ID+"", ""HPD:HelpDesk" ".""Case Type""" & Chr(13) & "" & Chr(10) & "FROM ""HPD:HelpDesk" " ""HPD:Hel" _
, _
"pDesk""" & Chr(13) & "" & Chr(10) & "WHERE (""HPD:HelpDesk "".""Arriva l Time"">={ts '" & StartDate & "'} And ""HPD:HelpDesk" ".""Arrival Time""<={ts '" & EndDate & "'}) AND (""HPD:HelpDesk "".""Case Type""='Inciden t')" _
)

Here StartDate and EndDate are of string type. requirement is , we need data for the range of start date and end date.

Thanks in advance
GK

[ck9663]

When you say you hard code the date, did you remove the "{" symbol or not?

Another thing, try to display the value of those variables and see if it's in the proper date format (ie YYYY--MM-DD).

I got some articles about dates here, just search the Article labels.

-- CK

[iam_clint]

you may try casting your string

cast(date as datetime)

[kalragarvita]

you may try casting your string

cast(date as datetime)

Thanks Buddy it is working now.
All I did change variables into String and uses the excell format:)

BUT now another worry..
very same issue

With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=AR System ODBC Data Source;ARServer=slonp46d;UID=srivastg;;ARAuthentication=;SERVER=NotTheServer" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT ""HPD:HelpDesk"".""Assigned To Group+"", ""HPD:HelpDesk"".""Assigned To Individual+"", ""HPD:HelpDesk"".""Case ID+"", ""HPD:HelpDesk"".""Case Type"", ""HPD:HelpDesk"".Category, ""HPD:HelpDesk"".""Create Time"", ""HPD" _
        , _
        ":HelpDesk"".EMI_Department, ""HPD:HelpDesk"".EMI_Operating_Commitee" & Chr(13) & "" & Chr(10) & "FROM ""HPD:HelpDesk"" ""HPD:Hel" _
        , _
        "pDesk""" & Chr(13) & "" & Chr(10) & "WHERE (""HPD:HelpDesk"".""Arrival Time"">={ts '" & StartDate & "'} And ""HPD:HelpDesk"".""Arrival Time""<={ts '" & EndDate & "'}) AND (""HPD:HelpDesk"".""Case Type""='Incident') And ""HPD:HelpDesk"".EMI_Department='" & Department & "' And ""HPD:HelpDesk"".EMI_Operating_Commitee='" & OperatingCommitee & "'" _
        )
        .Name = "new querytest"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True

in above code: OperatingCommit ee which is string variable is giving "Runtime error 13"

Plz help