Pass Range Reference as query Parameter

[turbovr6mang]

A new statistic has been requested for a weekly report which will require pulling a query from an Access database. I'm attempting to mimic an existing VBA procedure that pulls a sum of emails sent based on a date variable (and some other criteria).

I simply want to pass some dates (stored on the margin of the report worksheet) as parameters for the query, but can't get the syntax right. At this point, I'm getting a debug error "Undefined function 'Range' in expression.

I'm a total noob to Access and pretty fragile with VBA so if you have any tips for getting up to speed with Excel/Access integration, it would be much appreciated.

Here's my code:
Sub_GetFunded()

'Set source
MyConn = "\\dir\file"


'Select Output Range
Sheets("Wkly_v2").Select
Range("Q2").Activate

'Get Records for Funded
sSQL = "SELECT Count(qryFunded.Acct) AS CountOfAcct " & _
"FROM qryCSRPT_Funded_Subquery " & _
"WHERE ((qryCSRPT_Funded_Subquery.created >= Range('S2')) And (qryCSRPT_Funded_Subquery.FundDate >= Range('S4')));"

'Create RecordSet for Emails Sent Query
Set Cn = New ADODB.Connection
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
Set Rs = .Execute(sSQL)
End With

ActiveCell.Offset(4, 4).Select

If Not (Rs.BOF And Rs.EOF) Then
For Each MyField In Rs.Fields
ActiveCell.Value = MyField
Next MyField
End If

End Sub

Many thanks for your expertise!

 

[james_lankford]

"WHERE ( (qryCSRPT_Funded_Subquery.created >= #" & Range("S2") & "#) And (qryCSRPT_Funded_Subquery.FundDate >= #" & Range("S4") & "# ));"