VBA Replace... then Error

[Desu Nota from Columbus]

I got the following code to run (it pulls data from a SQL Server based on the SQL Code line)

Sub PullData()

Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Server_Name = "GGVORNE\SQLEXPRESS" ' Enter your server name here
Database_Name = "VorneDB" ' Enter your database name here
User_ID = "vorne" ' enter your user ID here
Password = "VData!!!" ' Enter your password here
SQLStr = "Select * From [VorneDB].[dbo].[interval_stream]" ' Enter your SQL here

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic
' Dump to spreadsheet
With Worksheets("sheet1").Range("a1:z50000") ' Enter your sheet name and range here
    .ClearContents
    .CopyFromRecordset rs
End With
'            Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub

I tried to add another facet to this working macro. I want to input two parameters in A1 and A2 (start time, end time) and apply these to the SQL Command line with a where statement. I tried the following (changes are in blue):

Sub PullData()

Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim LResultA As String
Dim LResultB As String

LResultA = Replace("Variable1", "Variable1", Range("A1")) 
LResultB = Replace("Variable2", "Variable2", Range("A2"))

Server_Name = "GGVORNE\SQLEXPRESS" ' Enter your server name here
Database_Name = "VorneDB" ' Enter your database name here
User_ID = "vorne" ' enter your user ID here
Password = "VData!!!" ' Enter your password here
SQLStr = "Select * From [VorneDB].[dbo].[interval_stream] Where [start_time] >= 'Variable1' and [end_time] < 'Variable2'" ' Enter your SQL here

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic
' Dump to spreadsheet
With Worksheets("sheet1").Range("a1:z50000") ' Enter your sheet name and range here
    .ClearContents
    .CopyFromRecordset rs
End With
'            Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub

I get the error code,

Run-Time Error '-2147217913
[Microsoft][ODBC SQL Server Driver][SQL Server] Conversion failed when converting datetime from character string

The line: rs.Open SQLStr, Cn, adOpenStatic is highlighted.

The format of the datetime for SQL is the following string 'yyyy-mm-dd hh:mm:ss.sss' and I matched the contents of A1 and A2 to follow that requirement exactly (formatted as Text).

When I use the following in the code, the macro works perfectly:

SQLStr = "Select * From [VorneDB].[dbo].[interval_stream] Where [start_time] >= '2011-06-03 07:00:00.000' and [end_time] < '2011-06-06 07:00:00.000'" ' Enter your SQL here

So this leads me to believe that the error is coming from using the RangeA1/A2.

Once the data is imported into excel (using a manual import) excel sees the datetime as m/d/yyyy h:mm

I think this is all the information I can provide.

I am not familiar with userforms, but if you were asked to input the start time and end time manually that might work.

Any ideas or work arounds?

 

[xenou]

Your use of variables is incorrect.

Here's an attempt at solving this, if A1 and A2 have the dates. Our aim is simply to construct a good SQL Statement which we pass to the database engine. You can view your resulting SQL string with a msgbox or debug print statement.

I hope this makes sense enough and you see where to put it in your code. Drop the stuff about Variable1 and Variable2 and LResultA and LResultB, which is somewhat confused as it stands.

Note that depending on what you put in the cell A2 for endDate, you want to watch it - the assumption is that it's one day past the boundary date, but you can also use the real endDate in the cell and add 1 to it in your code (which is more natural for users who don't need to think about adding 1 to dates).

Dim startDate As Date
Dim endDate As Date
startDate = Range("A1").Value
endDate = Range("A2").Value
SQLStr = ""
SQLStr = SQLStr & " SELECT * FROM [VorneDB].[dbo].[interval_stream] "
SQLStr = SQLStr & " WHERE [start_time] >= '" & Format(startDate, "yyyy-mm-dd hh:nn:ss.000") & "'"
SQLStr = SQLStr & " AND [end_time] < '" & Format(endDate, "yyyy-mm-dd hh:nn:ss.000") & "'"
SQLStr = ";"
MsgBox SQLStr
Debug.Print SQLStr

 

[Desu Nota from Columbus]

Your use of variables is incorrect.

Here's an attempt at solving this, if A1 and A2 have the dates. Our aim is simply to construct a good SQL Statement which we pass to the database engine. You can view your resulting SQL string with a msgbox or debug print statement.

I hope this makes sense enough and you see where to put it in your code. Drop the stuff about Variable1 and Variable2 and LResultA and LResultB, which is somewhat confused as it stands.

Note that depending on what you put in the cell A2 for endDate, you want to watch it - the assumption is that it's one day past the boundary date, but you can also use the real endDate in the cell and add 1 to it in your code (which is more natural for users who don't need to think about adding 1 to dates).

Dim startDate As Date
Dim endDate As Date
startDate = Range("A1").Value
endDate = Range("A2").Value
SQLStr = ""
SQLStr = SQLStr & " SELECT * FROM [VorneDB].[dbo].[interval_stream] "
SQLStr = SQLStr & " WHERE [start_time] >= '" & Format(startDate, "yyyy-mm-dd hh:nn:ss.000") & "'"
SQLStr = SQLStr & " AND [end_time] < '" & Format(endDate, "yyyy-mm-dd hh:nn:ss.000") & "'"
SQLStr = ";"
MsgBox SQLStr
Debug.Print SQLStr

Thanks a bunch!!! I really love the MsgBox idea.

I had to make one edit for this to work. I deleted the SQLStr = ";" line.

Are you able to explain what I did wrong with my replace idea with Variable1 and variable2? Im trying to be more self-sufficient and learn VBA and it always helps to learn why something didn't work.

Thanks again.

 

[xenou]

No problem. With the variables, I've assumed that Range A1 and A2 have the start and end dates.

I'm not actually sure why you have used these two variables at all:

[COLOR=Navy]LResultA [/COLOR]= Replace("Variable1", "Variable1", Range("A1")) 
[COLOR=Navy]LResultB [/COLOR]= Replace("Variable2", "Variable2", Range("A2"))

Or what "Variable1" and "Variable2" has to do with the values in the cells or the replace formula.

It's rather garbled I'm afraid - where to begin? I thought it best to scratch and start over! Probably the simplest way to use variables is as in my example previously. You may have to give it a think and try to figure out what you wanted to do, and then figure out why your first attempt didn't get it done... No worries though - debugging your own code is basically the same thing as learning VBA!

 

[Desu Nota from Columbus]

No problem. With the variables, I've assumed that Range A1 and A2 have the start and end dates.

I'm not actually sure why you have used these two variables at all:

[COLOR=Navy]LResultA [/COLOR]= Replace("Variable1", "Variable1", Range("A1")) 
[COLOR=Navy]LResultB [/COLOR]= Replace("Variable2", "Variable2", Range("A2"))

Or what "Variable1" and "Variable2" has to do with the values in the cells or the replace formula.

It's rather garbled I'm afraid - where to begin? I thought it best to scratch and start over! Probably the simplest way to use variables is as in my example previously. You may have to give it a think and try to figure out what you wanted to do, and then figure out why your first attempt didn't get it done... No worries though - debugging your own code is basically the same thing as learning VBA!

HAHAHAHAHAAHAHHAHAAHH I am an idiot...

I see what I did, and corrected it. I think that was a product of a long day staring at a computer screen

I was attempting to substitute in A1 and A2 for Variable1 and Variable2 in the SQL statement and I didn't quite grasp the concept...