Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: SQL statement is give me error 3061 Too few parameters. Expected 1

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SQL statement is give me error 3061 Too few parameters. Expected 1

    The SQL statement works in the query grid but not in the vba. I want a search box to pop up and I will enter the EquipID into the search box then it will load the correct records into a list box for that EquipID. See below for the SQL Statement.

    strSQL = "SELECT tblTag.TagNumber, tblTag.TagID, tblEquipment.Equipment, tblEquipment.EquipID " & vbCrLf & _
    "FROM tblEquipment INNER JOIN tblTag ON tblEquipment.EquipID = tblTag.EquipID " & vbCrLf & _
    "WHERE (((tblEquipment.EquipID) Like ['Filter Report: Type EquipID]));"
    Last edited by bama4954; Aug 14th, 2019 at 02:51 PM.

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,467
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    Is this supposed to be a form field or some control value? Does it really have a single leading quote in it?

    ['Filter Report: Type EquipID]


    Edit:
    Note that if it is a form field then the form should be open and the field should have a value in it.


    Edit again:
    I tried your query and it works fine as is (I get a pop up to enter in the equip id - the like criteria is effectively an equals however, since there is no actual wildcard matching here).
    Last edited by xenou; Aug 14th, 2019 at 05:26 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    No leading quote. It is a search box that pops up when the query is executed. However it will not work in VBA. I tried creating a form also with a combo box and got the same error. Too few parameters. Expected 1

  4. #4
    Board Regular
    Join Date
    Jul 2010
    Posts
    445
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    VBA expects the parameter to be passed to it. Instead of having the query pop up and ask for the parameter, do it before hand. Also there is no need for your VbCrLF unless you are wanting to have the strSQL displayed formatted at some point. Try something like:

    Code:
    Dim myparam As String
    myparam = InputBox("Filter Report: Type EquipID", "Enter Filter")
    
    
    strSQL = "SELECT tblTag.TagNumber, tblTag.TagID, tblEquipment.Equipment, tblEquipment.EquipID " _
    & "FROM tblEquipment INNER JOIN tblTag ON tblEquipment.EquipID = tblTag.EquipID " _
    & "WHERE tblEquipment.EquipID Like '" & myparam & "'"
    Last edited by stumac; Aug 15th, 2019 at 05:12 AM.

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,467
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    I would suggest you show how you are using strSQL when you get the error.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #6
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    stumac you nailed it!! Thank you so much!
    Question: So if I wanted to change the InputBox to a combo box and load it with the actual Equipment Number, not the ID. What changes would I need to make? The name of the Combo Box is cboEquipNumber.

  7. #7
    Board Regular
    Join Date
    Jul 2010
    Posts
    445
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    assuming the code is triggered by an event on the same page as the combo box:

    Code:
    strSQL = "SELECT tblTag.TagNumber, tblTag.TagID, tblEquipment.Equipment, tblEquipment.EquipID " _
    & "FROM tblEquipment INNER JOIN tblTag ON tblEquipment.EquipID = tblTag.EquipID " _
    & "WHERE tblEquipment.EquipID = '" & me.cboEquipNumber & "'"
    Note that you no longer need the string myparam. If the code is triggered from another event (i.e. not on the same form) then you would need to qualify the form name: [Forms]![form name]![cboEquipNumber]

  8. #8
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    I have it working when I select a piece of equipment from the combo box. But when I add the [Forms]![frmTagReport]![cboEquipNumber] in the grid of the underlying query it gives me a mismatch error. I think it has something to do with the quotes in the sql statement but still learning and not sure. I also tried changing the binding of the combo box to the primary key, then to the equipment text, but none of them worked.

  9. #9
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    I solved that problem with the mismatch error. Now it is populating the list box with the correct records but it is not populating the report but the correct records are displayed on the underlying query when I run it. Almost there!

  10. #10
    Board Regular
    Join Date
    Jul 2010
    Posts
    445
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    You haven't given us all of the code, only the bit where you are building a query string.

    Is the report data source being set to strSQL?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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