robertrobert905
Board Regular
- Joined
- Jun 27, 2008
- Messages
- 139
Hi I have a caldendar control that gets the variables StartDateString and EndDateString which i dimmed as Dates
the form then calls 3 separate modules, ICTMain, ICTTask and WOMD which are data queries from Remedy
when i don't pass the variables StartDateString, EndDateString then I'm good, but when I add the dates in there like above, I get an error
this is the code it got stuck on, (very bottom)
the form then calls 3 separate modules, ICTMain, ICTTask and WOMD which are data queries from Remedy
Code:
Public Sub OKButton_Click()
Dim Remuserid As String
Dim Rempass As String
Dim StartDateString As Date
Dim EndDateString As Date
RemedyLogin.Hide
Remuserid = RemedyLogin.Remedy_User.Value
Rempass = RemedyLogin.Remedy_Pass.Value
StartDateString = Format(frmCalendar2.StartDate.Text, "yyyy-mm-dd")
EndDateString = Format(frmCalendar2.EndDate.Text, "yyyy-mm-dd")
Call ICTMAIN(Remuserid, Rempass, StartDateString, EndDateString)
Call ICTTasks(Remuserid, Rempass, StartDateString, EndDateString)
Call WOMD(Remuserid, Rempass, StartDateString, EndDateString)
End Sub
when i don't pass the variables StartDateString, EndDateString then I'm good, but when I add the dates in there like above, I get an error
this is the code it got stuck on, (very bottom)
Code:
Sub ICTMAIN(RemId, RemPassword, StartDate As Date, EndDate As Date)
'
Sheets.Add.Name = "ICT Main"
Sheets("ICT Main").Select
'
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Remedy ODBC Server;ARServer=172.19.249.147;UID=" & RemId & ";PWD=" & RemPassword & ";ARAuthentication=;SERVER=NotTheServer" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT ""Incident Main"".""Incident ID"", ""Incident Main"".Identifier, ""Incident Main"".Category, ""Incident Main"".Chronic, ""Incident Main"".""Device Type"", ""Incident Main"".""Device Scope"", ""Incident Main"".""IP " _
, _
"Address"", ""Incident Main"".Item, ""Incident Main"".Node, ""Incident Main"".""No of Customer Complaints"", ""Incident Main"".""Outage Indicator"", ""Incident Main"".""Potential Cause"", ""Incident Main"".""Potential Cau" _
, _
"se Type"", ""Incident Main"".""Potential Cause Element"", ""Incident Main"".""Resolution Category"", ""Incident Main"".""Resolution Type"", ""Incident Main"".""Incident Start"", ""Incident Main"".""Incident End"", ""Incide" _
, _
"nt Main"".""Service Impact End"", ""Incident Main"".Region, ""Incident Main"".Site, ""Incident Main"".""Outage Region"", ""Incident Main"".Summary, ""Incident Main"".Status, ""Incident Main"".Type" & Chr(13) & "" & Chr(10) & "FROM ""Incident Main" _
, _
""" ""Incident Main""" & Chr(13) & "" & Chr(10) & "WHERE (""Incident Main"".""Service Impact End"">={ts '2009-11-01 00:00:00'})" _
)
.Name = "Query from Remedy ODBC Server"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
[B][COLOR=red].Refresh BackgroundQuery:=False[/COLOR][/B]
End With
Call ICTMain_Edit
End Sub