468,316 Members | 2,059 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

Query For Excel Macro

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
5 3314
ck9663
2,878 Expert 2GB
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
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
2,878 Expert 2GB
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
1,208 Expert 1GB
you may try casting your string

cast(date as datetime)
Sep 26 '08 #5
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.

Similar topics

4 posts views Thread by Jenni | last post: by
3 posts views Thread by newkid | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.