too few parameters

[slimjen1]

All, using 2010. I am using a form with unbound fields to filter for a report to preview or export to excel. I want both options. But when I select fields to filter i get "two few parameters expect 3" when i click the preview button. If I fill 4 of the unbound fields; I get exected 4. It seems to go up the more fields i fill. I've never seen this. Here is the code:

Private Sub cmdPrintPreview_Click()
On Error GoTo Err_cmdPrintPreview_Click

Dim strSQL As String, intCounter As Integer
Dim db As Database, rs As Recordset
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.
    If Me.cboWEdateFrom & vbNullString <> "" And Me.cboWEdateTo & vbNullString <> "" Then
        strSQL = strSQL & " ([Week Ending] BETWEEN " & Me.cboWEdateFrom & " And " & Me.cboWEdateTo & ") And "

    End If

    strnewquery = "Select qryQAReport.* FROM qryQAReport"

    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.OpenReport "rptQAReport", acViewPreview, , strSQL
     
          'DoCmd.OutputTo acOutputQuery, "qryQAReportQuery", acFormatXLS, True
        DoCmd.Close acForm, "frmReportbuilder"
    Else
        MsgBox "There are no records that match your criteria! Please select new criteria.", vbInformation
        Exit Sub
    End If

Exit_cmdPrintPreview_Click:
        Exit Sub

Err_cmdPrintPreview_Click:
    Select Case Err.Number
        Case 2501 'OpenQuery action was cancelled
            Resume Exit_cmdPrintPreview_Click
        Case Else
            MsgBox Err.Description
            Resume Exit_cmdPrintPreview_Click
            Resume
        End Select
End Sub

Can anyone help please?

 

[pbaldy]

This is a commonly used tool to debug the problem:

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

 

[slimjen1]

Thanks. I was using print debug but wasnt paying attention. I use this code in another database and compared both printouts. I did noticed that this line of code:

' Set the value of the parameter.
    If Me.cboWEdateFrom & vbNullString <> "" And Me.cboWEdateTo & vbNullString <> "" Then
        strSQL = strSQL & " ([Week Ending] BETWEEN " & Me.cboWEdateFrom & " And " & Me.cboWEdateTo & ") And "

It all printed Example:

Select qryQAReport.* FROM qryQAReport WHERE [cboWEdateFrom] like "6/1/13" And [cboWEdateTo] like '6/2/13" and ([Week Ending] BETWEEN #6/1/13# and #6/2/13#

It's not supposed to print the first part just the last part that begins with ([Week Ending]) I am using the exact same code. Why is it doing this? thks

 

[JHB]

Be sure that [cboWEdateFrom], [cboWEdateTo] and ([Week Ending] are fields included in the qryQAReport query/table

 

[pbaldy]

Looks like your date controls are included in the loop above, and shouldn't be.

 

[slimjen1]

Ok. I think i know what's wrong. You're correct Pbaldy; it seems my dates for the IF statement is being evaluated along with the field on the report:

 ' Set the value of the parameter.
    If cboWEdateFrom & vbNullString <> "" And cboWEdateTo & vbNullString <> "" Then
        strSQL = strSQL & " ([Week Ending] BETWEEN #" & cboWEdateFrom & "# And #" & cboWEdateTo & "#) And "

    End If

The field on the form is a cbo box with a recordsource from the query of the date field([Week Ending]) but I think it should be without a recordsource so the user can put in the date range they want?? Can a date be a string? thus "vbNullString"

I am using code from another database I designed but I don't remember what I did to get this to work. Help Thanks

 

[pbaldy]

I think that part is fine, it's above in the loop testing the tag property that the date controls are being handled incorrectly.

 

[slimjen1]

I can't seem to get it going. Do you think the fact that im using this code in 2010 have anything to do with it? I have tried numerous search examples and still nothing. It keeps evaluating the cboWEdateFrom..... See debug print out:

Select qryQAReport.* FROM qryQAReport WHERE [cboSelect]  like "OPERATIONS" And [cboWEdateFrom]  like "6/1/2013" And [cboWEdateTo]  like "6/5/2013" And ([Week Ending] >= 6/1/2013) AND ([Week Ending] >= 6/5/2013);

Keep getting error "too few parameters 1, 2, 3 etc depending on how many fields i select.

 

[pbaldy]

For starters, clear the tag property of the date combos so the loop ignores them. Second, for things in the loop the field names would have to be identical to the control names. Are they? It doesn't appear so.

 

[slimjen1]

Got it!!! Thanks so much! I have to pay more attention to the field names
Thanks again!!

 

[pbaldy]

Happy to help!

 

[slimjen1]

Now that I can preview; I am trying to export to excel using the same params but it gives me everything instead of using the criteria on the form. Any ideas? This is the code for preview:

 DoCmd.OpenReport "rptQAReport", acViewPreview, , strSQL

but when I use the same for exporting; it returns everything

DoCmd.OutputTo acOutputReport, , "rptQAReport", acFormatXLS, True

if I use this; it tells me "wrong data type for one of the arguments

DoCmd.OutputTo acOutputReport, , "rptQAReport", acFormatXLS,  strSQL

 

[pbaldy]

I know you cross posted this, so I'll be brief. OutputTo doesn't have that argument. You can try exporting after opening filtered in preview mode. I know SendObject will send the filtered report in that situation, and OutputTo may as well.