SELECT * from problem

[DianeA]

Pretty green to coding but i have a simular macro running in excel which does the same thing except the WHERE stmt compares the field to a date variable. In this case I'm trying to filter out records from an ACCESS query with a particular month found in a specific cell in excel. Once found it grabs the number from the access query and plops it into excel. But I'm getting error "No value given for one or more of the required parameters". It works if i substitute PULLMONTH with a specific value i.e. "12"

Hope u can assist
Diane

------------------------------------------------
Sub PullCSMC()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, PullDate As Date, PullMonth As Single, PullYear As Single
Dim StrQstr As String

On Error GoTo Errorhandler

' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=G:\\Prices.mdb;"

' open a recordset
Set rs = New ADODB.Recordset

PullDate = Range("B3").Value
PullMonth = Month(PullDate)
PullYear = Year(PullDate)

StrQstr = "SELECT * from qryCSMCbyHourTTLS WHERE qrymonth = PullMonth"

With rs

.Open StrQstr, cn, adOpenForwardOnly, adLockReadOnly

Range("Q21").Value = .Fields("CSMC")

End With


rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Exit Sub

 

[SkipVought]

Hi,

StrQstr = "SELECT * from qryCSMCbyHourTTLS WHERE qrymonth =" &  PullMonth

or

StrQstr = "SELECT * from qryCSMCbyHourTTLS WHERE qrymonth =" &  Month([B3].Value)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DianeA]

This worked great... i did forget to add the year though and obviously i am also having a problem with it. This was my best guess... sorry.. pretty lame

StrQstr = "SELECT * from qryCSMCbyHourTTLS WHERE qryCSMCbyHourTTLS.qrymonth =" & PullMonth And "qryCSMCbyHourTTLS.qryyear =" & PullYear

thanks for your assistance
Diane

 

[DianeA]

I finally got it..

thanks for your help
diane