getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see 3

[cdck]

I'm using VBA to build a query based on responses to a number of fields on a form, two of which are listboxes. When the code executes, it appears to build the query correctly, but I get the subject error every time.

I've set a watch on the query, and when I copy the watch value and paste it directly into a new query, I get valid results.

Here is the watch value:
SELECT * FROM qryTSAcomments WHERE ([tsStatus] = 'I' OR [tsStatus] = 'A' OR [tsStatus] = 'M') AND ([Dep] = 40 OR [Dep] = 70 OR [Dep] = 90);

It tells me "Too few parameters, expected 2."

If I drop this into a new query window and run it as is, it gives me 342 results.




Cheryl dc Kern

 

[LaurieHamlin]

I've seen this error when a field name is incorrect.

 

[cdck]

I have, too, which is why I tried pasting it into a query and running it that way. Usually, if you do that, you get a more direct error response which tells you which field you typed wrong. But in this case, it runs just fine.

Cheryl dc Kern

 

[Andrzejek]

Try this way instead and see if you have a better luck:
[tt]
SELECT * FROM qryTSAcomments
WHERE (tsStatus IN ('I', 'A', 'M'))
AND (Dep IN (40, 70, 90));
[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[cdck]

Andy:

This is actually the way I had it structure the query first, with the same result.

I'm having the listbox update a text field each time an item is selected or deselected to create the code which will be pulled by the VBA that builds the query. Here's one of the two:

Private Sub lstDeps_Click()
Dim SelectedValues As String
Dim frm As Form
Dim varItem As Variant
Dim lstItems As Control
Set lstItems = Me!lstDeps
 
For Each varItem In lstItems.ItemsSelected
    If SelectedValues > "" Then
        SelectedValues = SelectedValues & " OR [Dep] = " & lstItems.ItemData(varItem)
    Else
       SelectedValues = "[Dep] = " & lstItems.ItemData(varItem)
    End If
Next varItem

Me!SelectedValues = SelectedValues
End Sub

This was originally written to result in code to support Andy's suggestion, and was re-written when I couldn't get past this error.

Here's the code that pulls it all together when the user is through making selections and is ready to run the report:

Private Sub cmdPull_Click()
Dim UserName As String
UserName = GetUserName()
    Dim strSql As String
    strSql = "SELECT * FROM qryTSAcomments"

    Dim WhereAnd As String
    WhereAnd = " WHERE "
    
    Dim strANDS As String
    strANDS = ""

    Select Case optJoSoOther
        Case Is = 1
            strANDS = strANDS & " WHERE [tsJob] = '" & Me.cbJob & "'"
            WhereAnd = " AND "
        Case Is = 2
            strANDS = strANDS & " WHERE [fsono] = '" & Me.cbSO & "'"
            WhereAnd = " AND "
    End Select
    
    Dim strStat As String
    Select Case optStat
        Case Is = 1
            strStat = ""
        Case Is = 2
        If IsNull(Me.txtSelectedStats) = True Then
                MsgBox ("You must either select 'Any Status' or at least one status out of the list.")
                Exit Sub
            Else
                strStat = "(" & Me.txtSelectedStats & ")"
            End If
    End Select
    
    If Len(strStat) > 0 Then
        If WhereAnd = " WHERE " Then
            strANDS = strANDS & " WHERE " & strStat
            WhereAnd = " AND "
        Else
            strANDS = strANDS & " AND " & strStat
        End If
    End If
    
    Dim strDept As String
    Select Case optDept
        Case Is = 1
            strDept = ""
        Case Is = 2
            If IsNull(Me.SelectedValues) = True Then
                MsgBox ("You must either select 'All Departments' or at least one department out of the list.")
                Exit Sub
            Else
                strDept = "(" & Me.SelectedValues & ")"
            End If
    End Select
    
    If Len(strDept) > 0 Then
        If WhereAnd = " WHERE " Then
            strANDS = strANDS & " WHERE " & strDept
            WhereAnd = " AND "
        Else
            strANDS = strANDS & " AND " & strDept
        End If
    End If
    
    Dim strKeys As String
    If Me.txtKeys > "" Then
        strKeys = "[Comment] LIKE '%" & Me.txtKeys & "%'"
    End If
        
    If Len(strKeys) > 0 Then
        If WhereAnd = " WHERE " Then
            strANDS = strANDS & " WHERE " & strKeys
        Else
            strANDS = strANDS & " AND " & strKeys
        End If
    End If
    
    If WhereAnd = " WHERE " Then
        strSql = strSql & ";"
    Else
        strSql = strSql & strANDS & ";"
    End If
    
    Dim dbs As Database
    Dim rs As Recordset
    Dim qdf As QueryDef
    
    Set dbs = CurrentDb()
    Set rs = dbs.OpenRecordset(strSql, dbOpenSnapshot)

    With dbs
        Set qdf = .CreateQueryDef(UserName, strSql)
        DoCmd.OpenQuery UserName
        .QueryDefs.Delete UserName
    End With
    dbs.Close
    qdf.Close
End Sub

Am I missing a point where I should be forcing some sort of format? I just can't understand why the same query that errors when it hits the OpenQuery stage here runs fine if copied straight into a query window and run there.

Cheryl dc Kern

 

[dhookom]

Add a line of code that returns your full SQL and add DAO.

    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Debug.Print strSql

Duane
Hook'D on Access
MS Access MVP

 

[cdck]

I made the changes recommended by dhookom and got the same results. With the settings in place on the form this time, the Immediate window showed:

SELECT * FROM qryTSAcomments WHERE ([tsStatus] = 'I' OR [tsStatus] = 'M' OR [tsStatus] = 'A');

Still "Too few parameters. Expected 2." in the error window, still retrieved results without editing when pasted into a new query window.

Cheryl dc Kern

 

[dhookom]

You have two references to controls on forms in qryTSAcomments.

Duane
Hook'D on Access
MS Access MVP

 

[cdck]

Yes, here's how the second one is set (pretty much the same as the first).

Private Sub lstStats_Click()
Dim SelectedValues As String
Dim frm As Form
Dim varItem As Variant
Dim lstItems As Control
Set lstItems = Me!lstStats
 
For Each varItem In lstItems.ItemsSelected
    If SelectedValues > "" Then
        SelectedValues = SelectedValues & " OR [tsStatus] = '" & Left(lstItems.ItemData(varItem), 1) & "'"
    Else
       SelectedValues = "[tsStatus] = '" & Left(lstItems.ItemData(varItem), 1) & "'"
    End If
Next varItem

If Me.ckNoMans = False Then
    If Len(SelectedValues) > 0 Then
        SelectedValues = SelectedValues & " OR [tsStatus] = 'M'"
    Else
        SelectedValues = SelectedValues & "[tsStatus] = 'M'"
    End If
End If

Me!txtSelectedStats = SelectedValues
End Sub

The CASE statements in the code above determine if that control needs to be used or not. So for instance, if the option to filter by Departments is set correctly, it would involve the selected values set by the departments listbox:

    Dim strDept As String
    Select Case optDept
        Case Is = 1
            strDept = ""
        Case Is = 2
            If IsNull(Me.SelectedValues) = True Then
                MsgBox ("You must either select 'All Departments' or at least one department out of the list.")
                Exit Sub
            Else
                strDept = "(" & Me.SelectedValues & ")"
            End If
    End Select
    
    If Len(strDept) > 0 Then
        If WhereAnd = " WHERE " Then
            strANDS = strANDS & " WHERE " & strDept
            WhereAnd = " AND "
        Else
            strANDS = strANDS & " AND " & strDept
        End If
    End If

There's a similar section of code to deal with the option to filter based on status.

With both options set to be used, I run it again and get this Query:
SELECT * FROM qryTSAcomments WHERE ([tsStatus] = 'I' OR [tsStatus] = 'M' OR [tsStatus] = 'A') AND ([Dep] = 49 OR [Dep] = 54 OR [Dep] = 70);

Same results.

Cheryl dc Kern

 

[dhookom]

So what is the SQL view of qryTSAcomments? If that has more references to queries then dig deeper until you find a reference like:

FORMS!frmName!ControlName



Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Just a side note...
In your code you manipulate and build your [tt]strSQL[/tt] and you have 10 places where you keep track of [tt]" WHERE " [/tt]part of your Select.

Consider this approach:

Private Sub cmdPull_Click()
Dim UserName As String
UserName = GetUserName()
    Dim strSql As String
    strSql = "SELECT * FROM qryTSAcomments [blue]WHERE 1 = 1[/blue] "
...

This way you don't have to worry about where to put[tt] WHERE[/tt] any more

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[cdck]

Andrzejek: Thanks for the suggestion, it helped me to clean up the code by several lines.

dhookom: The query this pulls from did, in fact, reference two date fields on the form. I'd put them in there to reduce the total number of records the built query had to evaluate. When I removed those references from the target query and built them into this one, it then ran fine, but very very slow. Is that slowness just the reality I have to live with, or is there a way to make this work more efficiently?

Here's the target query as it stood previously:

SELECT dbo_vwTSFullSheetNonPTOitemsWsos.tsJob, CInt([Dept]) AS Dep, CDate([effDate]) AS efDate, 
dbo_vwTSFullSheetNonPTOitemsWsos.tsOp, dbo_vwTSFullSheetNonPTOitemsWsos.tsWorkCenter, 
dbo_vwTSFullSheetNonPTOitemsWsos.tsStatus, dbo_vwTSFullSheetNonPTOitemsWsos.Hrs, 
dbo_vwTSFullSheetNonPTOitemsWsos.fsono, dbo_vwTSFullSheetNonPTOitemsWsos.Comment, 
dbo_vwTSFullSheetNonPTOitemsWsos.EmpNo, dbo_vwTSFullSheetNonPTOitemsWsos.LnameFirst
FROM dbo_vwTSFullSheetNonPTOitemsWsos
WHERE (((CDate([effDate])) Between CDate([Forms]![TS Application Jobs and Comments].[txtDateFrom].[value]) 
And CDate([Forms]![TS Application Jobs and Comments].[txtDateTo].[value])));

I basically removed the WHERE clause, moving it to the start of the built query, thus:

    Dim strSql As String
    strSql = "SELECT * FROM qryTSAcomments WHERE efDate BETWEEN #" & dtFrom & "# AND #" & dtTo & "#"

and built on from there (without the efDate filter here, I would have gone with the 1 = 1 suggestion).

Cheryl dc Kern

 

[dhookom]

cdck,
It looks like all of the tables in qryTSAcomments are from SQL server. I would create a pass-through query to SQL server and dynamically change the SQL property:

[COLOR=#4E9A06]' in the code of [Forms]![TS Application Jobs and Comments]
' assumes qryTSAcomments is your pass-through[/color]
Dim strSQL as String
strSQL = "SELECT tsJob, Convert(INT,Dept) Dep, Convert(DateTime,effDate) efDate, " & _
  "tsOp, tsWorkCenter, tsStatus, Hrs, fsono, Comment, EmpNo, LnameFirst " & _
  "FROM vwTSFullSheetNonPTOitemsWsos " & _
  "WHERE Convert(DateTime,effDate) Between '" & Me.[txtDateFrom] & "' And '" & Me.[txtDateTo] & "'"
Currentdb.QueryDefs("qryTSAcomments").SQL = strSQL

You will be very impressed by the speed gain.

Duane
Hook'D on Access
MS Access MVP

 

[cdck]

dhookom:

This is a front-end which multiple users will be accessing, possibly at the same time. If I set the pass-through query and then pull from it in each user's instance of the database as they execute the process, will it cause conflicts if it happens to fire at the same time? Is it possible that one user's output will be corrupted by settings selected by another user?

Cheryl dc Kern

 

[dhookom]

You shouldn't use my suggestion if multiple users are in the same front end. However, users should NEVER share the same front end.

Duane
Hook'D on Access
MS Access MVP