David Megnin
asked on
Incorrect syntax near '#'. in ADODB.Connection in Excel workbook
With the block of code below in the Sheet Code of an Excel workbook I'm getting this error when I change the values
Run-time error '-2147217900 (80040e14)':
Incorrect syntax near '#'.
I copied and pasted this text from another sheet where it works fine:
"WHERE (CLSDDATE BETWEEN #" & Format(fromDate, "mm/dd/yyyy") & "# AND #" & Format(toDate, "mm/dd/yyyy") & "#)" & _
I'm not sure why I'm getting the syntax error.
Run-time error '-2147217900 (80040e14)':
Incorrect syntax near '#'.
I copied and pasted this text from another sheet where it works fine:
"WHERE (CLSDDATE BETWEEN #" & Format(fromDate, "mm/dd/yyyy") & "# AND #" & Format(toDate, "mm/dd/yyyy") & "#)" & _
I'm not sure why I'm getting the syntax error.
Dim dbConnection As ADODB.Connection
Set dbConnection = New ADODB.Connection
Dim connStr As String
connStr = "myWonderfulConnectionString"
dbConnection.ConnectionString = connStr
dbConnection.Open
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
Dim sql As String
Dim fromDate As Range
Set fromDate = ActiveSheet.Range("B1")
Dim toDate As Range
Set toDate = ActiveSheet.Range("D1"
sql = "SELECT [WO_NUM]" & _
",[CLSDBY]" & _
",[CLSDDATE]" & _
"FROM [TRACKIT8_from7].[dbo].[TASKS] " & _
"WHERE (CLSDDATE BETWEEN #" & Format(fromDate, "mm/dd/yyyy") & "# AND #" & Format(toDate, "mm/dd/yyyy") & "#)" & _
"OR CLSDDATE IS NULL"
With rsData
.ActiveConnection = dbConnection
.Open sql
If Not rsData.EOF Then
Worksheets("TrackIt").Range("A3").CopyFromRecordset rsData
'ActiveSheet.Range("C6").CopyFromRecordset rsData
End If
End With
you dont need that # in your query...
ASKER
I put them in because I wasn't getting the correct date range in the result so I thought that was the problem.
I've tweaked it a bit here and there. Let me try it now without the '#'.
I've tweaked it a bit here and there. Let me try it now without the '#'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Correct VBA syntax?
"WHERE (CLSDDATE BETWEEN '" & Format(fromDate, "mm/dd/yyyy") & "' AND '" & Format(toDate, "mm/dd/yyyy") & "')" & _
You almost can't see where I added a ' right next to a "
"WHERE (CLSDDATE BETWEEN '" & Format(fromDate, "mm/dd/yyyy") & "' AND '" & Format(toDate, "mm/dd/yyyy") & "')" & _
You almost can't see where I added a ' right next to a "
ASKER
Oh, that did it. :-D
ASKER
Olemo, the single quotes is what it needed as in my "Correct VBA syntax?" comment.
Great. And the code looks better if posted in a Code Snippet box, because the single and double quotes are distinctive there.
ASKER
Thank you!
ASKER
Oh. Thanks. I'll remember that. :-)
Oh, yeah, they do stand out.
Oh, yeah, they do stand out.
"WHERE (CLSDDATE BETWEEN '" & Format(fromDate, "mm/dd/yyyy") & "' AND '" & Format(toDate, "mm/dd/yyyy") & "')"
"At your service"