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

[mohanvijay]

use ' [single quation] instead of '#'

'#' is used for Ms Access

[Pogla]

[vba]strSQL = "SELECT * FROM DP_REACTIVE_JOB_VIEW_COMPLETED_BY_PERIOD " & _
"WHERE completed >= #" & strfrom_date & "# And completed >= #" & strto_date & "#"[/vba]

would not work in SQL Server

The # symbol is reserved for temporarary tables.

Your code should be:

[vba]strSQL = "SELECT * FROM DP_REACTIVE_JOB_VIEW_COMPLETED_BY_PERIOD " & _
"WHERE completed >= '" & strfrom_date & "' And completed >= '" & strto_date & "'"[/vba]

I would avoid using SELECT * unless you are 100% absolutely certain that you want the whole table, and the table structrue would not change.

I would also make sure that the source columns are actually dates that SQL can read, something like:

[VBA]strfrom_date = Cells(2, 4)

IF strfrom_date <> "" THEN
CheckDate = TypeName(Cells(2, 4))
IF CheckDate <> "DATE" THEN
****error code here****
END IF
ELSE
****error code here****
END IF[/VBA]

There is a SQL Server keyword of BETWEEN, however it is the same as

[vba]WHERE completed > '" & strfrom_date & "' And completed > '" & strto_date & "'" [/vba]
i.e. it looks for values greater than and less than, not including the boundary values