SQL string syntex problem

[davepro62]

Hi guys

I am having a problem with the syntax of a SQL query. I am comnnecting to a view in a MS SQL database and I want to use a WHERE clause to define a Date span.

I am entering 2 dates on the cells of an Excel sheet and then declearing them to 2 Date type variables.

The code is erroring out when I try and open the connection telling me [Microsofr][ODBC SQL SERVER DRIVER][SQL SERVER][SYNTAX ERROR NEAR '#'.

' Create a connection object.
Dim cnOneServe As ADODB.Connection
Set cnOneServe = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

strConn = "Driver={SQL Server};Server=ROM-DB1-2K3; Database=OneServe_queries; UID=sa; PWD=sql"


'Now open the connection.
cnOneServe.Open strConn

' Create a recordset object.
Dim rsOneServer As ADODB.Recordset
Set rsOneServer = New ADODB.Recordset
Dim strSQL As String
Dim strfrom_date As Date
Dim strto_date As Date

strfrom_date = Cells(2, 4)
strto_date = Cells(3, 4)

strSQL = "SELECT * FROM DP_REACTIVE_JOB_VIEW_COMPLETED_BY_PERIOD " & _
"WHERE completed >= #" & strfrom_date & "# And completed >= #" & strto_date & "#"
'
'
Debug.Print strSQL

With rsOneServer
' Assign the Connection object.
.ActiveConnection = cnOneServe


' Extract the required records.
.Open strSQL, cnOneServe, adOpenForwardOnly

I know this is something simple but I am going round in circles.

Thanks in advance

 

[jmcilhinney]

If you're going to use date literals wrapped in # symbols then they MUST be in M/dd/yyyy format. I think that it's safe to assume that yours are not. I don;t think that SQL Server accepts date literals using # though. I would suggest using single quotes and using yyyy-MM-dd format. That said, I strongly suggest that you use parameters and then formatting is not an issue at all. I'm not going to go into that further though, because this is a VB.NET forum and your code is VB6.