too few parameters; expected 2

slimjen · #1 (**permalink**) 07-19-12, 16:18

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

Code:

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

sps · #2 (**permalink**) 07-19-12, 16:55

try setting a breakpoint in the code and walking thru it. This will allow you to see the resulting string values and tell you the exact line that is causing the error.

What is the final value of strnewquery at the debug.print line?

About half way down (' Set the value of the parameter.) you use a select case statement to set the value of strnewquery. Then after the last case you just set it again rendering the case statement pointless.

slimjen · #3 (**permalink**) 07-19-12, 21:13

I made corrections to the code:

Code:

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

The error in the immediate window is the microsoft access database engine cannot find the input table or query"WHERE ([MONTHPROCESSED] BETWEEN JUNE And JULY);" Monthprocessed is the column in my filter along with the dates. I know I need help with the code please. Thank you

sps · #4 (**permalink**) 07-20-12, 09:37

Quote:

Originally Posted by sps

What is the final value of strnewquery at the debug.print line?

The error message is obviously related to the sql statement. I can't say much about it without knowing what it is. Please put a breakpoint in the code and copy the resulting sql statement so we can take a better look at it.

They way the error reads, you have a where clause in the sql at a point when the database is expecting a table.

slimjen · #5 (**permalink**) 07-20-12, 12:49

I was trying to get the code to work.

The error is now Type mismatched at the line:

Code:

DoCmd.OpenQuery "qrySPReports_test", acViewNormal, strSQL

This is the sql statement in the immediate window:

Code:

Select qrySPReports_test.* FROM qrySPReports_test

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 also have an alias formating the date to month and on my form, users can select and from and to month. Maybe thats a problem too. The field is not actually format mmmm yyyy it's mmddyyyy.
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

Code:

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.* FROM qrySPReports_test"

    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", 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"
        DoCmd.OpenQuery "qrySPReports_test", acViewPreview, strSQL
        DoCmd.Close acForm, "frmREPORTBUILDER"
            stDocName = "qrySPReports_test"
            DoCmd.OpenQuery stDocName, acPreview
            DoCmd.OutputTo acOutputQuery, qrySPReports_test, acFormatXLS
            DoCmd.Close acQuery, "qrySPReports_test"

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

sps · #6 (**permalink**) 07-20-12, 14:01

Quote:

Originally Posted by slimjen

The error is now Type mismatched at the line:

Code:

DoCmd.OpenQuery "qrySPReports_test", acViewNormal, strSQL

First of all, you can't do this. The DoCmd.OpenQuery is intended to open a query that already exists in the database and doesn't allow for a sql statement to be added as a parameter.

You can use: DoCmd.OpenQuery "qrySPReports_test", acViewNormal to open the query named qrySPReports_test but the sql statement isn't allowed.

If you look at the result of this statement you will notice that it doesn't properly build a like statement. You are missing the *

Code:

strSQL = strSQL & "[" & ctl.Name & "] " & " like " 
& Chr(34) & ctl.Value & Chr(34) & " And "

You would need to do this instead (to add the * around the criteria)

Code:

strSQL = strSQL & "[" & ctl.Name & "] " & " like "
& Chr(34) & "*" & ctl.Value & "*" & Chr(34) & " And "

After that you really need to take some time and clean up your code. It would probably help if you set a breakpoint and walk thru your code to see what it is doing. A couple issues:

Quote:

Originally Posted by slimjen

Code:

Set rs = db.OpenRecordset(strnewquery)
        If rs.RecordCount > 0 Then
            DoCmd.OpenQuery "qrySPReports_test", 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"
        DoCmd.OpenQuery "qrySPReports_test", acViewPreview, strSQL
        DoCmd.Close acForm, "frmREPORTBUILDER"
            stDocName = "qrySPReports_test"
            DoCmd.OpenQuery stDocName, acPreview

You are opening a recordset just to see if the query returns results and then opening the same query.

But then you completely ignore this check and open the query up anyway after the if statement. Then you go on and open the same query for a third time!

Your export statement is using the base query anyway so all the work you have done to add the criteria in is pointless.

slimjen · #7 (**permalink**) 07-20-12, 14:21

Thanks Steve, I appreciate this. I will take your suggestions to rewrite and cleanup the code. Do you think you can suggest how I would include the case statement and the Year criteria? Do you think the way I am using the alias on the date field is returning errors also?
Thanks you so much for helping!

sps · #8 (**permalink**) 07-20-12, 15:57

Quote:

Originally Posted by slimjen

Do you think you can suggest how I would include the case statement and the Year criteria?

Really hard to say since your code is trying to build a sql statement using existing queries. If you are going to build a sql statement then in each case, add the appropriate criteria to the where clause based on the module chosen.

Quote:

Originally Posted by slimjen

Do you think the way I am using the alias on the date field is returning errors also?

Quote:

The error in the immediate window is the microsoft access database engine cannot find the input table or query"WHERE ([MONTHPROCESSED] BETWEEN JUNE And JULY);" Monthprocessed is the column in my filter along with the dates.

You can tell in the error message that your sql is being built using full month names. What I don't know is how the data is stored in MonthProcessed. But the error with this particular statement had nothing to do with the dates and everything to do with how the sql was formatted.

My best advice at this point: Start by thinking through the logic that you need to reach your goal, decide if you are going to use existing queries or build sql statements in vba, clean up the code, and take it one step at a time. When you get a specific question start a thread and get some help!

Internet Services

Web Development

Digital Marketing

Consumer Tech