Link to home
Start Free TrialLog in
Avatar of David Megnin
David MegninFlag for United States of America

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.
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

Open in new window

Avatar of tigin44
tigin44
Flag of Türkiye image

you dont need that # in your query...
Avatar of David Megnin

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 '#'.  
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 "
Oh, that did it.  :-D
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.
Thank you!
Oh.  Thanks.  I'll remember that.  :-)
Oh, yeah, they do stand out.

"WHERE (CLSDDATE BETWEEN '" & Format(fromDate, "mm/dd/yyyy") & "' AND '" & Format(toDate, "mm/dd/yyyy") & "')" 

Open in new window

"At your service"