cant solve run-time error 3061

[KiaruB]

Hello,

I cant see what the problem is whit my code, i hope someone else can.

I get the 3061 error, too few parameters (expected 2)

the SQL statement is not using any form fields directly
i did put the form fields into variables first (to avoid this 3061 error)

here is the code :

Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String
Dim startdat As Date
Dim einddat As Date
Dim dag As String
Dim strlist As String
Dim rs As DAO.Recordset
Set db = CurrentDb()

dag = [Forms]![GROEPSLESSEN]![Combo111]
startdat = [Forms]![GROEPSLESSEN]![Text10]
einddat = [Forms]![GROEPSLESSEN]![Text107]
startdat = Format(startdat, "mm/dd/yyyy")
einddat = Format(einddat, "mm/dd/yyyy")
strlist = ""

strSQL = "SELECT [BESCHIKBAARID] FROM [BESCHIKBAAR] WHERE [STARTDATUM] = #" _
& startdat & "# AND [EINDDATUM] = #" & Format$(einddat, "mm/dd/yyyy") _
& "# AND [WELKEDAG] = " & dag

If Not IsNull([Forms]![GROEPSLESSEN]![crit1]) Then
strSQL = strSQL & " AND [LESBLOK1] = B"
End If
If Not IsNull([Forms]![GROEPSLESSEN]![crit2]) Then
strSQL = strSQL & " AND [LESBLOK2] = B"
End If
If Not IsNull([Forms]![GROEPSLESSEN]![crit3]) Then
strSQL = strSQL & " AND [LESBLOK3] = B"
End If
If Not IsNull([Forms]![GROEPSLESSEN]![crit4]) Then
strSQL = strSQL & " AND [LESBLOK4] = B"
End If
If Not IsNull([Forms]![GROEPSLESSEN]![crit5]) Then
strSQL = strSQL & " AND [LESBLOK5] = B"
End If
If Not IsNull([Forms]![GROEPSLESSEN]![crit6]) Then
strSQL = strSQL & " AND [LESBLOK6] = B"
End If
If Not IsNull([Forms]![GROEPSLESSEN]![crit7]) Then
strSQL = strSQL & " AND [LESBLOK7] = B"
End If
If Not IsNull([Forms]![GROEPSLESSEN]![crit8]) Then
strSQL = strSQL & " AND [LESBLOK8] = B"
End If
If Not IsNull([Forms]![GROEPSLESSEN]![crit9]) Then
strSQL = strSQL & " AND [LESBLOK9] = B"
End If

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF

strlist = strlist & rs(0) & ";"

rs.MoveNext
Loop
End If


rs.Close
db.Close
Set rs = Nothing

Thanks!

 

[Remou]

Could it be:
[tt]If Not IsNull([Forms]![GROEPSLESSEN]![crit1]) Then
strSQL = strSQL & " AND [LESBLOK1] = B"
End If[/tt]
Maybe:
[tt]If Not IsNull([Forms]![GROEPSLESSEN]![crit1]) Then
strSQL = strSQL & " AND [LESBLOK1] = [red]'B'[/red]"
End If[/tt]

 

[KiaruB]

Thanks a lot Remou

problem solved

i was focusing on the SQL string itself

had to place dag between quotes too

now it works