Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: too few parameters; expected 2

    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

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    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.

    Steve

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    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
    Last edited by slimjen; 07-19-12 at 22:22.

  4. #4
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by sps View Post
    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.

    Steve

  5. #5
    Join Date
    Jul 2004
    Posts
    214
    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

  6. #6
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by slimjen View Post
    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 View Post
    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.

    Steve

  7. #7
    Join Date
    Jul 2004
    Posts
    214
    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!

  8. #8
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by slimjen View Post
    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 View Post
    Do you think the way I am using the alias on the date field is returning errors also?
    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!

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •