VBA pass date by varaiable error (1004)

[robertrobert905]

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

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)

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

 

[Norie]

Are you sure the date(s) are the problem?

You don't even seem to be using them in the code you've posted.

 

[robertrobert905]

sorry i made a mistake, the problem was that i kept entering the wrong username and password into remedy so that the connection can't be established,



oh and by the ways thanks for helping me with the 100 tectbox problem last week

 

[robertrobert905]

Now I have a different problem!

my remedy data retrival macro which i have recorded

if you look at the bottom the code : Service Impact End"">={ts '2009-11-01 00:00:00'})" _
)

is a filter that filters all the tickets by date. which i would like to be a variable (EndDate), but I have no idea how to incorporate that into the code

i tried replacing the static date and time with " & EndDate & " or ' & EndDate & ' and both gave me errors

 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"">={[B][COLOR=red]ts '2009-11-01 00:00:00'})" _
        )[/COLOR][/B]
 
        .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
        .Refresh BackgroundQuery:=False
    End With

 

[Norie]

I think the first thing you might want to do is sort out that SQL statement.

I'm sure it's come from the macro recorder, but I'm also sure it could be cleaned up.

All those quotes and Chr shouldn't be needed.

Once you've sorted that it should be pretty straightforward to include the variable(s).