too few parameters - expected 1

[miconian]

Not sure what I'm doing wrong here. According to the debugger, the problem is in the "Set rst" line.

Function AnythingThere(strCurrentSite As String)

Dim strSQL As String
Dim rst As DAO.Recordset
Dim intRecordCount As Integer

strSQL = "SELECT Analyzer.[Revenue Under Goal (Lifetime)], Analyzer.[Order Line], Analyzer.[End Date],"
strSQL = strSQL + "Analyzer.[Order ID] , Analyzer.[Billing Method] FROM Analyzer WHERE "
strSQL = strSQL + "(((Analyzer.[Revenue Under Goal (Lifetime)])>0)"
strSQL = strSQL + "AND ((Analyzer.[End Date])<=(Date())+7) AND ((Analyzer.[Billing Method])='Delivery')"
strSQL = strSQL + "AND ((Analyzer.[Internet Site])=" & strCurrentSite & "))"

Debug.Print strSQL
' Run a query to see whether it comes back with anything. If not, then no need to make a corresponding table for that site.

Set rst = CurrentDb.CreateQueryDef("", strSQL).OpenRecordset
intRecordCount = rst.RecordCount

AnythingThere = intRecordCount

rst.Close
Set rst = Nothing

End Function

 

[dsaffo]

Hi Miconian,

Try this:

Function AnythingThere(strCurrentSite As String)

Dim strSQL As String
Dim rst As DAO.Recordset
Dim intRecordCount As Integer
Dim qDef As New DAO.QueryDef

strSQL = "SELECT Analyzer.[Revenue Under Goal (Lifetime)], Analyzer.[Order Line], Analyzer.[End Date],"
strSQL = strSQL + "Analyzer.[Order ID] , Analyzer.[Billing Method] FROM Analyzer WHERE "
strSQL = strSQL + "(((Analyzer.[Revenue Under Goal (Lifetime)])>0)"
strSQL = strSQL + "AND ((Analyzer.[End Date])<=(Date())+7) AND ((Analyzer.[Billing Method])='Delivery')"
strSQL = strSQL + "AND ((Analyzer.[Internet Site])=" & strCurrentSite & "))"

Debug.Print strSQL
' Run a query to see whether it comes back with anything. If not, then no need to make a corresponding table for that site.

Set qDef = CurrentDb.CreateQueryDef("", strSQL)
Set rst = qDef.OpenRecordset
intRecordCount = rst.RecordCount

AnythingThere = intRecordCount

rst.Close
Set rst = Nothing

End Function

 

[miconian]

dsaffo: Thanks, but I tried that, and it did not solve the problem. I am still getting the same error on the same line.

 

[Kreszch68]

Hi, if you paste the SQL from the debug.print into the query designer, does this return any records? Or do you recieve the same message?

BTW: you don't have to create a temp querydef to open the recordset, just write CurrentDb.OpenRecordset(sSQL)

 

[Kreszch68]

Looking at the SQL, what's wrong for sure is that you're missing some spaces

strSQL = "SELECT Analyzer.[Revenue Under Goal (Lifetime)], Analyzer.[Order Line], Analyzer.[End Date],"
strSQL = strSQL + "Analyzer.[Order ID] , Analyzer.[Billing Method] FROM Analyzer WHERE "
strSQL = strSQL + "(((Analyzer.[Revenue Under Goal (Lifetime)])>0)[COLOR=#ff0000][B]{SPACE}[/B][/COLOR]"
strSQL = strSQL + "AND ((Analyzer.[End Date])<=(Date())+7) AND ((Analyzer.[Billing Method])='Delivery')[COLOR=#ff0000][B]{SPACE}[/B][/COLOR]"
strSQL = strSQL + "AND ((Analyzer.[Internet Site])='" & strCurrentSite & "'))"

And arround strCurrentSite change to ])='" & strCurrentSite & "'))", thus add the single quotes as it is a string value

 

[miconian]

Thanks, guys. The spaces did seem to be the issue. I have other issues with the code, but I am going to continue that conversation on the other thread.

 

[SydneyGeek]

Check the other thread for a generic way to deal with recordset parameters.

Denis