Too few parameters, expecting 1

[munkee]

All,

Using the following code to execute a query to count records:

Private Sub Command89_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String
Dim counter As Integer

Set db = CurrentDb



strsql = strsql & "SELECT Count(tblhselog.HSEID) AS CountOfHSEID "
strsql = strsql & "FROM tblhselog "
strsql = strsql & "WHERE (((Format([Incident_date],""mmm""))=" & Me.[cmboMonth] & ") AND ((Format([Incident_date],""yyyy""))=" & Me.[cmboYear] & ") AND ((tblhselog.Department)=" & Me.[cmboDept] & "));"


Debug.Print strsql
Set rs = db.OpenRecordset(strsql, dbOpenSnapshot)

  
counter = rs("CountOfHSEID")
MsgBox counter
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

The output of my debug.print strsql is:

SELECT Count(tblhselog.HSEID) AS CountOfHSEID FROM tblhselog WHERE (((Format([Incident_date],"mmm"))=Jan) AND ((Format([Incident_date],"yyyy"))=2011) AND ((tblhselog.Department)=8));

The error I am getting is the usual "Too few parameters expecting 1"

However as the debugp print shows all of the WHERE clause is correctly filled in with parameters (Jan, 2011, 8).

Anyone else spot the issue here?

Edit: Oh and for info I have had this query run fine within querydef when using forms.frmAccide ntQuad.cmbodept etc. But this failed when switched to vba, even when doubling up on quotes etc.

Thanks,

Chris

[Stewart Ross]

Hi Chris. You're missing single quote string delimiters in line 13 for the combo's text value:

strsql = strsql & "WHERE (((Format([Incident_date],""mmm"")) = '" & Me.[cmboMonth] & "' ) AND ((Format([Incident_date],""yyyy""))=" & Me.[cmboYear] & ") AND ((tblhselog.Department)=" & Me.[cmboDept] & "));"

Without the delimiters the text value 'Jan' will be treated as a field name - hence the missing parameter message.

-Stewart

[munkee]

Damn you are correct! I had actually been looking at that but instead of the delimiter ' I was using double quotes etc ".

Thanks for the quick response.