VBA pass date by varaiable error (1004)

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
33nz3n8.jpg


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

34eu5om.jpg


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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 27, 2008
Messages
139
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,

:oops: :oops:

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

robertrobert905

Board Regular
Joined
Jun 27, 2008
Messages
139
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



Code:
 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

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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).:)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,187
Messages
5,594,748
Members
413,930
Latest member
Nela817

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top