By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Query For Excel Macro

P: 4
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"".""Assigned To Group+"", ""HPD:HelpDesk"".""Assigned To Individual+"", ""HPD:HelpDesk"".""Assignee 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:HelpDesk"".""Case Type""='Incident') 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!
Sep 25 '08 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Sep 25 '08 #2

P: 4
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"".""Assigned To Group+"", ""HPD:HelpDesk"".""Assigned 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"".""Arrival Time"">={ts '" & StartDate & "'} And ""HPD:HelpDesk"".""Arrival Time""<={ts '" & EndDate & "'}) AND (""HPD:HelpDesk"".""Case Type""='Incident')" _
)

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
Sep 26 '08 #3

ck9663
Expert 2.5K+
P: 2,878
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
Sep 26 '08 #4

iam_clint
Expert 100+
P: 1,208
you may try casting your string

cast(date as datetime)
Sep 26 '08 #5

P: 4
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
Expand|Select|Wrap|Line Numbers
  1. With ActiveSheet.QueryTables.Add(Connection:= _
  2.         "ODBC;DSN=AR System ODBC Data Source;ARServer=slonp46d;UID=srivastg;;ARAuthentication=;SERVER=NotTheServer" _
  3.         , Destination:=Range("A1"))
  4.         .CommandText = Array( _
  5.         "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" _
  6.         , _
  7.         ":HelpDesk"".EMI_Department, ""HPD:HelpDesk"".EMI_Operating_Commitee" & Chr(13) & "" & Chr(10) & "FROM ""HPD:HelpDesk"" ""HPD:Hel" _
  8.         , _
  9.         "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 & "'" _
  10.         )
  11.         .Name = "new querytest"
  12.         .FieldNames = True
  13.         .RowNumbers = False
  14.         .FillAdjacentFormulas = False
  15.         .PreserveFormatting = True
  16.  
in above code: OperatingCommitee which is string variable is giving "Runtime error 13"

Plz help
Sep 29 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.