Too Few Parameters..!!! !!

[totomalas]

I am trying to run the following code, where a query runs by taking some values from a form, then a recordset is intialized to count the records from the query result....

but each time i run, i get this error

Error 3061: Too Few Parameters.Expe cted 2

my code:

Dim MyDB As DAO.Database
Dim rstEmployee As DAO.Recordset
Dim AdminArea As String
Dim Line As String
Dim Refining As Integer
Dim NAOilOpns As Integer
Dim MSP As Integer
Dim mysql As String
 
 
Set MyDB = CurrentDb
mysql = "SELECT [Admin Area].[admin Name], [Business Line].[Business Line  Name], [PTD Request].PTD_Request_Receive_date, [PTD Request].PTD_Status, Employee.[American?]FROM (([Business Line] INNER JOIN [Admin Area] ON [Business Line].[Business Line ID] = [Admin Area].[business line id]) INNER JOIN Employee ON [Admin Area].[admin ID] = Employee.[Admin id]) INNER JOIN [PTD Request] ON Employee.[Badge Number] = [PTD Request].[Badge Number] _
WHERE ((([PTD Request].PTD_Request_Receive_date) Between [forms]![report]![start] And [forms]![report]![end]) AND (([PTD Request].PTD_Status)=[forms]![report]![Received from source]) AND ((Employee.[American?])=[forms]![report]![Yes]));"
 
 
Set rstEmployee = MyDB.OpenRecordset(mysql)
 
 
Refining = 0
NAOilOpns = 0
MSP = 0
 
 
With rstEmployee
 Do While Not .EOF
 
Admin = ![Admin Name]
Line = ![Business Line  Name]
 
If Line = "E & P" Then
 
 
         If Admin = "Refining" Then
           Refining = Refining + 1
       Else
           If Admin = "M&SP" Then
           MSP = MSP + 1
           End If
         End If
Else
    If Line = "R M & I" Then
 
      NAOilOpns = NAOilOpns + 1
    End If
 
   End If
 
     .MoveNext
 
 Loop
End With
rstEmployee.Close
Set rstEmployee = Nothing
 
[Reports]![report1]![received_ref] = Refining
[Reports]![report1]![received_msp] = MSP
[Reports]![report1]![received_total] = MSP + Refining
[Reports]![report1]![received_oil] = NAOilOpns
 
End Sub

I am desperate to have this work...

[Stewart Ross]

Problem is your use of direct form control references in your WHERE clause. These are not fields as such, and when run from VBA the SQL interpreter treats them as parameters with no values. The simplest solution is to include the values of the form controls, not references to the form control names, in the SQL string. As you are using controls representing dates, which can be tricky to compare, I am using Format below to bring them to the mm/dd/yyyy format required for ANSI SQL date comparisons. If your dates are already in this format you will not need the Format function to do so.

WHERE ((([PTD Request].PTD_Request_Receive_date) Between #" _
& Format([forms]![report]![start], "mm/dd/yyyy") & "# And #" _
& Format([forms]![report]![end], "mm/dd/yyyy") & "#) AND _
(([PTD Request].PTD_Status) = '" & [forms]![report]![Received from source]) _ 
& "' AND ((Employee.[American?])= " & [forms]![report]![Yes]& "));"

I am also assuming that your PTD-Status field is of type Text - if not you will not need the single quotes before and after the value shown above.

For an alternate approach to this kind of issue please see post # 8 of this thread: Access does not recognise valid field name.

-Stewart