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

bama4954

New Member
Joined
Oct 31, 2018
Messages
35
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:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,587
Office Version
2013
Platform
Windows
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:

bama4954

New Member
Joined
Oct 31, 2018
Messages
35
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
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,587
Office Version
2013
Platform
Windows
I would suggest you show how you are using strSQL when you get the error.
 

bama4954

New Member
Joined
Oct 31, 2018
Messages
35
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.
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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]
 

bama4954

New Member
Joined
Oct 31, 2018
Messages
35
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.
 

bama4954

New Member
Joined
Oct 31, 2018
Messages
35
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!
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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?
 

Forum statistics

Threads
1,085,960
Messages
5,386,962
Members
402,026
Latest member
rudyd06

Some videos you may like

This Week's Hot Topics

Top