• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

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

    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") & "#)" & _

    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

  • 6
  • 3
1 Solution
you dont need that # in your query...
megninAuthor Commented:
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 '#'.  
QlemoC++ DeveloperCommented:
#date# is Access syntax. MSSQL needs single quotes ( 'date' ) around date literals.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

megninAuthor Commented:
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 "
megninAuthor Commented:
Oh, that did it.  :-D
megninAuthor Commented:
Olemo, the single quotes is what it needed as in my "Correct VBA syntax?" comment.
QlemoC++ DeveloperCommented:
Great. And the code looks better if posted in a Code Snippet box, because the single and double quotes are distinctive there.
megninAuthor Commented:
Thank you!
megninAuthor Commented:
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

QlemoC++ DeveloperCommented:
"At your service"

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now