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

[bama4954]

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]));"

 

[xenou]

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).

 

[bama4954]

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]

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:

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 & "'"

 

[xenou]

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

 

[bama4954]

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]

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

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]

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]

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]

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?