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:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
I would suggest you show how you are using strSQL when you get the error.
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top