too few parameters; expected 2

[slimjen]

Hi All, Using Access 2003 front end with Sql Server backend. I Have a form users can use to export a query to an excel spreadsheet . On the form are several unbound boxes: division, year, to month and from month. My query is a pass thru query. I am trying to let them choose the division and time frame of the query to output instead of outputting all the records in the query. I used this code before but running reports. I am trying to modify it to use a query instead. This is what I have so far. I am having difficulty referencing division and the year. I tried running to see what I needed but I can't get pass the error. Can someone guide me with this code. Thank you

Private Sub cmdExport_Click()
On Error GoTo Err_cmdExport_Click
Dim strSQL As String, intCounter As Integer
Dim ctl As Control, strname As String, strnewquery As String
Dim strRptSel As String
Dim stMessage As String
Set db = CurrentDb
      'Build SQL String
      For Each ctl In Me.Form
      If ctl.Tag = "input" Then
      'strname = "me." & ctl.Name
      If ctl.Value > "" Then
      strSQL = strSQL & "[" & ctl.Name & "] " & " like " & Chr(34) & ctl.Value & Chr(34) & " And "
      End If
      End If
     Next ctl
    
    ' Set the value of the parameter.
    Select Case Me.Module
    Case SP
       strnewquery = "[qrySPReports_test_passthru]"
    Case LTL
        strnewquery = "[qryLTLReports_test_passthru]"
    Case DTF
        strnewquery = "[qryDTFReports_test_passthru]"
    If Me.cboFROM & vbNullString <> "" And Me.cboTO & vbNullString <> "" Then
        strSQL = strSQL & " ([MONTHPROCESSED] BETWEEN " & Me.cboFROM & " And " & Me.cboTO & ") And "
    End If
    
    strnewquery = "Select qrySPReports_test_passthru.* FROM qrySPReports_test_passthru"
    If strSQL <> "" Then
        strSQL = Left(strSQL, (Len(strSQL) - 5))
        strnewquery = strnewquery & " WHERE " & strSQL & ";"
    End If
    Debug.Print strnewquery
        ' Create the recordset
        Set rs = db.OpenRecordset(strnewquery)
        If rs.RecordCount > 0 Then
            DoCmd.OpenQuery "[qrySPReports_test_passthru]", acViewNormal, strSQL
            DoCmd.Close acForm, "frmREPORTBUILDER"
        Else
            MsgBox "There are no records that match your criteria! Please select new criteria.", vbInformation
            Exit Sub
        End If
        DoCmd.Minimize
        stDocName = "[qrySPReports_test_passthru]"
        DoCmd.OpenQuery "[qrySPReports_test_passthru]", acViewNormal, strSQL
        DoCmd.Close acForm, "frmREPORTBUILDER"
            stDocName = "[qrySPReports_test_passthru]"
            DoCmd.OpenQuery stDocName, acPreview
            DoCmd.OutputTo acOutputQuery, [qrySPReports_test_passthru], acFormatXLS
            DoCmd.Close "[qrySPReports_test_passthru]"
Exit_cmdExport_Click:
    Exit Sub
Err_cmdExport_Click:
    Select Case Err.Number
        Case 2501 'OpenQuery action was cancelled
            Resume Exit_cmdExport_Click
        Case Else
            MsgBox Err.Description
            Resume Exit_cmdExport_Click
            Resume
        End Select
End Sub

[pbaldy]

On what line? What is the result of


Debug.Print strnewquery

[ssanfu]

It look like you are missing an "End Select" statement:

    Select Case Me.Module
    Case SP
       strnewquery = "[qrySPReports_test_passthru]"
    Case LTL
        strnewquery = "[qryLTLReports_test_passthru]"
    Case DTF
        strnewquery = "[qryDTFReports_test_passthru]"
    End Select 

[pbaldy]

I didn't notice that, but presumably that's a cut/paste oversight. If it wasn't there, I expect you'd get a compile error, not the parameters error.

[slimjen]

thank you for responding. The debug print didn't error. I stepped through the code and it errored out at Set rs = db.OpenRecordset(strnewquery).
Thanks
And; yes end statement go lost in the copy and paste. Thanks

[pbaldy]

You must not realize what that's for:

http://www.baldyweb.com/ImmediateWindow.htm

[slimjen]

Thanks for the reminder. Guess Im frustrated with this code not working when I've used it before. I made some corrections to the code when I reread the case statement and now The error is The microsoft access database engine cannot find the input table or query"WHERE ([MONTHPROCESSED] BETWEEN JUNE And JULY);" This is a date column. I selected SP for the module and June and July dates for the records to be returned. I'm still missing something but what. Thanks

Private Sub cmdExport_Click()
On Error GoTo Err_cmdExport_Click
Dim strSQL As String, intCounter As Integer
Dim ctl As Control, strname As String, strnewquery As String
Dim strRptSel As String
Dim stMessage As String
Set db = CurrentDb
      'Build SQL String
      For Each ctl In Me.Form
      If ctl.Tag = "input" Then
      'strname = "me." & ctl.Name
      If ctl.Value > "" Then
      strSQL = strSQL & "[" & ctl.Name & "] " & " like " & Chr(34) & ctl.Value & Chr(34) & " And "
      End If
      End If
     Next ctl
    
    ' Set the value of the parameter.
    Select Case Me.Module
    Case SP
       strnewquery = "[qrySPReports_test_passthru]"
    Case LTL
        strnewquery = "[qryLTLReports_test_passthru]"
    Case DTF
        strnewquery = "[qryDTFReports_test_passthru]"
    End Select
    
    If Me.cboFROM & vbNullString <> "" And Me.cboTO & vbNullString <> "" Then
        strSQL = strSQL & " ([MONTHPROCESSED] BETWEEN " & Me.cboFROM & " And " & Me.cboTO & ") And "
    End If
    
    'strnewquery = "Select qrySPReports_test_passthru.* FROM qrySPReports_test_passthru"
    If strSQL <> "" Then
        strSQL = Left(strSQL, (Len(strSQL) - 5))
        strnewquery = strnewquery & " WHERE " & strSQL & ";"
    End If
    Debug.Print strnewquery
        ' Create the recordset
        Set rs = db.OpenRecordset(strnewquery)
        If rs.RecordCount > 0 Then
            DoCmd.OpenQuery strnewquery, acViewNormal, strSQL
            DoCmd.Close acForm, "frmREPORTBUILDER"
        Else
            MsgBox "There are no records that match your criteria! Please select new criteria.", vbInformation
            Exit Sub
        End If
        DoCmd.Minimize
        stDocName = strnewquery
        DoCmd.OpenQuery strnewquery, acViewNormal, strSQL
        DoCmd.Close acForm, "frmREPORTBUILDER"
            stDocName = strnewquery
            DoCmd.OpenQuery stDocName, acPreview
            DoCmd.OutputTo acOutputQuery, strnewquery, acFormatXLS
            DoCmd.Close strnewquery
Exit_cmdExport_Click:
    Exit Sub
Err_cmdExport_Click:
    Select Case Err.Number
        Case 2501 'OpenQuery action was cancelled
            Resume Exit_cmdExport_Click
        Case Else
            MsgBox Err.Description
            Resume Exit_cmdExport_Click
            Resume
        End Select
End Sub

[pbaldy]

Do you not want to post the SQL returned by the debug statement?

[slimjen]

It states: The microsoft access database engine cannot find the input table or query"WHERE ([MONTHPROCESSED] BETWEEN JUNE And JULY)

The query is a pass thru query and "MONTHPROCESSED" is an alias: MONTHPROCESSED: Format([dteSHIPDTE],"mmmm yyyy")

Maybe this is causing the errors? The query runs fine outside the form. I need to be able to select the time period without a popup asking for the date.
Thanks so much

[pbaldy]

That's what the error is. I'm asking what SQL is being printed to the Immediate window.

On another front, I don't think a date in that format is a good way to filter. alphabetic sorting will be problematic I would think.

[slimjen]

I apologize if Im not following. I am copy/pasting out of the immediate window:"WHERE ([MONTHPROCESSED] BETWEEN JUNE And JULY)
This is what is being printed.

Should I just have a popup window for the date then? What do you suggest to get the result I am after?
Thanks so much for helping.

[pbaldy]

Okay, then as you can see you're not building a complete, valid SQL statement for the recordset. You appear to have commented out the line that would have started the statement.

[slimjen]

I was trying to get the code to work. My original code that worked for me was built with no case statement( only one division) and for a report. Now I have multiple divisions(modules to choose and a query based on the module) I go turned around on how to write the code to include this and multiple years to choose from also. I need help as you can see to get this to work. Thanks so much for sticking with this. Any suggestions would be most appreciated.
Thanks

[ssanfu]

I looked at your code and made a few changes.
Not sure what you are trying to do; seeing the original code code would help.
Here is what I have:

Private Sub cmdExport_Click()
   On Error GoTo Err_cmdExport_Click

   Dim db As DAO.Database
   Dim RS As DAO.Recordset

   Dim strSQL As String, intCounter As Integer
   Dim ctl As Control, strname As String, strCriteria As String
   Dim strRptSel As String
   Dim stMessage As String

   Set db = CurrentDb

   'Build SQL String
   Select Case Me.Module
      Case SP
         strnewquery = "[qrySPReports_test_passthru]"
      Case LTL
         strnewquery = "[qryLTLReports_test_passthru]"
      Case DTF
         strnewquery = "[qryDTFReports_test_passthru]"
   End Select
   'create select clause
   strSQL = "Select " & strnewquery & ".* FROM " & strnewquery
   'strnewquery = "Select qrySPReports_test_passthru.* FROM qrySPReports_test_passthru"

   For Each ctl In Me.Form
      If ctl.Tag = "input" Then
         If ctl.Value > "" Then
            strCriteria = strCriteria & "[" & ctl.Name & "] " & " like " & Chr(34) & ctl.Value & Chr(34) & " And "
         End If
      End If
   Next ctl

   ' Set the value of the parameter.
   If Me.cboFROM & vbNullString <> "" And Me.cboTO & vbNullString <> "" Then
      strCriteria = strCriteria & " ([MONTHPROCESSED] BETWEEN " & Me.cboFROM & " And " & Me.cboTO & ") And "
   End If

   If strCriteria <> "" Then
      strCriteria = Left(strCriteria, (Len(strCriteria) - 5))
      strSQL = strSQL & " WHERE " & strCriteria & ";"
   End If

   Debug.Print strSQL

   ' Create the recordset

   '************************
   'not sure what you are trying to do here
   'why open a record set AND open the query
   '************************
   Set RS = db.OpenRecordset(strnewquery)
   If RS.RecordCount > 0 Then
      DoCmd.OpenQuery strSQL, acViewNormal
      DoCmd.Close acForm, "frmREPORTBUILDER"

      DoCmd.Minimize
      'opened the query a second time???
      stDocName = strnewquery
      DoCmd.OpenQuery strSQL, acViewNormal
      DoCmd.Close acForm, "frmREPORTBUILDER"

      'don't need to open the query before outputting
      stDocName = strnewquery
      DoCmd.OpenQuery stDocName, acPreview

      DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLS
      DoCmd.Close acQuery, stDocName

   Else
      MsgBox "There are no records that match your criteria! Please select new criteria.", vbInformation
   End If


   RS.Close
   Set RS = Nothing
   Set db = Nothing

Exit_cmdExport_Click:
   Exit Sub
Err_cmdExport_Click:
   Select Case Err.Number
      Case 2501   'OpenQuery action was cancelled
         Resume Exit_cmdExport_Click
      Case Else
         MsgBox Err.Description
         Resume Exit_cmdExport_Click
         Resume
   End Select
End Sub