ms-accessvbams-access-2013pass-through

Pass Through Query With Parameters


I am attempting to run a Access 2013 pass through query that takes two parameters from my VBA syntax. When I run this, I get a compile error of

expected end of statement
" and "

How should this sub be modified in order for this to be valid sql string?

Public Sub GeneratePassThroughForJob()

    Dim qdfPassThrough As DAO.QueryDef, MyDB As Database
    Dim strConnect As String, d1 As String, d2 As String, Dim SQL As String

    d1 = Format(Forms!DataPull!txtd1, "YYYY-MM-DD")
    d2 = Format(Forms!DataPull!txtd2, "YYYY-MM-DD")

    If Not IsNull(CurrentDb.QueryDefs("qrySQLPass").SQL) Then
        CurrentDb.QueryDefs.Delete "qrySQLPass"
    End If

    Set MyDB = CurrentDb()
    Set qdfPassThrough = MyDB.CreateQueryDef("qrySQLPass")
    strConnect = "ValidSQLServerConnectionString"

    qdfPassThrough.Connect = "ODBC;" & strConnect

    SQL = "Select fname, lname, address from einfo where startdate between "&d1&" and "&d2&""

    qdfPassThrough.SQL = "Select fname, lname, address from einfo where startdate between "&d1&" and "&d2&""

    qdfPassThrough.ReturnsRecords = False
    qdfPassThrough.Close

    Application.RefreshDatabaseWindow

    DoCmd.OpenQuery "qrySQLPass", acViewNormal, acReadOnly
    DoCmd.Maximize
End Sub

Solution

  • Dates should be quoted for SQL Server, and don't forget the spaces:

    SQL = "Select fname, lname, address from einfo where startdate between '" & d1 & "' and '" & d2 & "'"