MS Access runtime error '3061' too few parameters expected 2

0
votes

I have been playing about with this vba/sql code for a while now and I cant figure out why I keep getting the error 'MS Access runtime error '3061' too few parameters expected 2', Is there something I am missing?. I am trying to count how many records are in reviews_programme table with a schedule of 'B' (Which is part of a joined table that is BFMA_Tasklist and joined on the task field), which is between 2 dates which are input from a form. The result will then be displayed in a textbox on the form.

Private Sub cmdstats_Click()
Dim sSQL As String
Dim db As Database
Dim rs As DAO.Recordset

sSQL = "SELECT Count(*) AS [CountOfScheduleB] " & _
"FROM [BFMA_TaskList] INNER JOIN [Reviews_Programme] ON [BFMA_TaskList].[Task] = [Reviews_Programme].[Task] " & _
"WHERE [BFMA_TaskList].[Schedule]=""B"" AND [Reviews_Programme].[Planned_Date] Between Me![txtstatsfrom] And Me![txtstatsto];"


Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL)


If rs.RecordCount > 0 Then
   Me.Text2 = rs![CountOfScheduleB]
Else
   Me.Text2 = "N/A"
End If

Set rs = Nothing
Set db = Nothing


End Sub

Any help will be greatly appreciated. Thank you.

2
sqlvbams-accesscount
When you have a problem with SQL, use Debug.Print ssql to copy/paste the SQL into the a blank query editor (in SQL mode, obviously) and then it will be much easier to find the problem. "Missing Parameters" usually mean you specified something (that you probably thought was the name of a field in the table, etc) that the database doesn't recognize. Typo's etc. Two in this case. - ashleedawg
Also instead of using double-double-quotes "" in a SQL query, you can substitute single quotes '. Either way, get your query working in an Access Query first and then copy/paste the SQL over to VBA. - ashleedawg

2 Answers

1
votes

Try this:

sSQL = "SELECT Count(*) AS [CountOfScheduleB] " & _
"FROM [BFMA_TaskList] INNER JOIN [Reviews_Programme] ON [BFMA_TaskList].[Task] = [Reviews_Programme].[Task] " & _
"WHERE [BFMA_TaskList].[Schedule]=""B"" AND [Reviews_Programme].[Planned_Date] Between #" & Format(Me![txtstatsfrom],"mm\/dd\/yyy") & "# And #" & Format(Me![txtstatsto],"mm\/dd\/yyy") & "#;"
0
votes

1st of all, where is currentDB? u need make db connection for this before call its table.

2nd i think is 'B' is enough no need ""B""

3th u need to used '#' as date parameter. and format the text cos we all know all text is string. u need to convert it. like format(data,"format as") and remember it will better if this format same as database.

so the code should like be:

Private Sub cmdstats_Click()
Dim sSQL As String
Dim db As Database
Dim rs As DAO.Recordset

sSQL = "SELECT Count(*) AS [CountOfScheduleB] " & _
"FROM [BFMA_TaskList] INNER JOIN [Reviews_Programme] ON [BFMA_TaskList].[Task] = [Reviews_Programme].[Task] " & _
"WHERE [BFMA_TaskList].[Schedule]='B' AND [Reviews_Programme].[Planned_Date] Between #' & format(Me![txtstatsfrom],"dd/mm/yyyy") & '# And  #' & format(Me![txtstatsto],"dd/mm/yyyy") & #';"


'Set db = CurrentDb
 Set db = OpenDatabase("Your database)
 Set rs = db.OpenRecordset(sSQL)


If rs.RecordCount > 0 Then
   Me.Text2 = rs![CountOfScheduleB]
Else
   Me.Text2 = "N/A"
End If

Set rs = Nothing
Set db = Nothing


End Sub

or

Private Sub cmdstats_Click()
Dim sSQL As String
Dim db As Database
Dim rs As DAO.Recordset

sSQL = "SELECT Count(*) AS [CountOfScheduleB] " & _
"FROM [BFMA_TaskList] INNER JOIN [Reviews_Programme] ON [BFMA_TaskList].[Task] = [Reviews_Programme].[Task] " & _
"WHERE [BFMA_TaskList].[Schedule]='B' AND [Reviews_Programme].[Planned_Date] Between '" & format(Me![txtstatsfrom],"dd/mm/yyyy") & "' And  '" & format(Me![txtstatsto],"dd/mm/yyyy") & "';"


'Set db = CurrentDb
 Set db = OpenDatabase("Your database)
 Set rs = db.OpenRecordset(sSQL)


If rs.RecordCount > 0 Then
   Me.Text2 = rs![CountOfScheduleB]
Else
   Me.Text2 = "N/A"
End If

Set rs = Nothing
Set db = Nothing


End Sub

If you sure of what you done, u can remove "Me." cos me is form as its. sometimes we need "Me" sometimes Not.

Happy Coding.