MS Excel VBA with sql - Error: Incorrect syntax near keyword "between"

[BxWill]

Timely insight needed.

Using a relatively long sql script within MS Excel vba to extract data from a Sql Server database.

If I input the sql script on one line, as displayed below, I am able to run without any errors

SQL = "Select top 10 AccountNo,Name,Debit_Start,Debit_End from Sales where Process_Date between '2014-04-01' and '2015-03-31' and Invoice_No = '123457' Group by AccountNo,Name,Debit_Start,Debit_End"

However, if I input the sql script on multiple lines, as displayed below, I continue to receive the error
"Incorrect syntax near the keyword between."

SQL = "Select top 10 AccountNo,Name,Debit_Start,Debit_End" & _
"from Sales " & _
"where Process_Date between '2014-04-01' and '2015-03-31' " & _
"and Invoice_No = '123457' " & _
"Group by AccountNo,Name,Debit_Start,Debit_End"

The cause of the error appears to be related to the format of the dates, but I cannot just yet resolve.

I did review the list of references and do indeed have "Microsoft Active Data Objects 2.8 Library."

What modifications are needed to successfully run the sql script?

 

[dhookom]

DAO likes # for date delimiters and you are missing a space between Debit_End and from.

SQL = "Select top 10 AccountNo,Name,Debit_Start,Debit_End[highlight #FCE94F] [/highlight]" & _
"from Sales " & _
"where Process_Date between #2014-04-01# and #2015-03-31# " & _
"and Invoice_No = '123457' " & _
"Group by AccountNo,Name,Debit_Start,Debit_End"

Duane
Hook'D on Access
MS Access MVP

 

[BxWill]

Did make the modifications and still receive the error.


If I can run the sql script when the sql script is on one line but cannot run it when the sql script
is spread out among multiple lines, I don't understand why I need to modify by including a "#" before the dates.

It appears that there is a data type issue...


Upon receiving the error, this line is highlighted

RS.Open SQL, CN, adOpenStatic, adLockReadOnly, adCmdText  <--------- Highlighted after error

The full code is below;

Option Explicit
Private CN As ADODB.Connection

Function Connect(Server As String, _
                 Database As String) As Boolean
 
    Set CN = New ADODB.Connection
    On Error Resume Next
 
    With CN
        ' Create connecting string
        .ConnectionString = "Provider=SQLOLEDB.1;" & _
                            "Integrated Security=SSPI;" & _
                            "Server=" & Server & ";" & _
                            "Database=" & Database & ";"
        ' Open connection
        .Open
    End With
    ' Check connection state
    If CN.State = 0 Then
        Connect = False
    Else
        Connect = True
    End If
 
End Function

Function Query(SQL As String)
 
    Dim RS As ADODB.Recordset
    Dim Field As ADODB.Field
 
    Dim Col As Long
 
    ' Open up a recordset / run query
    Set RS = New ADODB.Recordset
    RS.Open SQL, CN, adOpenStatic, adLockReadOnly, adCmdText  <--------- Highlighted after error
 
    If RS.State Then
        Col = 1
        ' Output the column headings in the first row
        For Each Field In RS.Fields
            Cells(1, Col) = Field.Name
            Col = Col + 1
        Next Field
        ' Output the results in the rest of the worksheet
        Cells(2, 1).CopyFromRecordset RS
        Set RS = Nothing
    End If
End Function

Function Disconnect()
    ' Close connection
    CN.Close
End Function

Public Sub Run()
 
    Dim SQL As String
    Dim Connected As Boolean

    SQL = "Select top 10 AccountNo,Name,Debit_Start,Debit_End..."

    Connected = Connect("ServerName", "DatabaseName")
 
    If Connected Then
        ' If connected run query and disconnect
        Call Query(SQL)
        Call Disconnect
    Else
        ' Couldn't connect
        MsgBox "Could Not Connect!"
    End If
 
End Sub

Any additional insight?

 

[BxWill]

Found the problem!

Thanks for the insight.

 

[Andrzejek]

And the problem was....?

Others may benefit from your experience.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.