Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
cdck

cdck

Programmer
Nov 25, 2003
281
US
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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:

Code: 
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:

Code: 
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Yes, here's how the second one is set (pretty much the same as the first).
Code: 
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:
Code: 
    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
 
Upvote 0 Downvote
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:

Code: 
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.
 
Upvote 0 Downvote
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:
Code: 
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:
Code: 
    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
 
Upvote 0 Downvote
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:

Code: 
[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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Locked
  • Helpful tip
Capture Shift Key if Pressed on A form
Replies
3
Views
287
strongm
strongm
Moss100
  • Locked
  • Question
Vba code I can call from a query to reformat text
Replies
8
Views
265
Moss100
Moss100
Kennelbloke
  • Locked
  • Question
TransferSpreadsheet 3125 Error
Replies
5
Views
958
Andrzejek
Andrzejek
ziffer68
  • Locked
  • Question
Pass Through query to create an alias for DB2 member
Replies
0
Views
1K
ziffer68
ziffer68
Zyborg
  • Question
Using a variable as reference to a form
Replies
5
Views
257
Zyborg
Zyborg

Part and Inventory Search

Sponsor

Back
Top