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
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 & "'"