Using Like % and form control to build SQL Stmt.

[mapfax]

Can anyone help me with this? I am trying to work out the quotes, syntax etc to set a rowsource on a form using VBA. The SQL rowsource would look something like:

SELECT colA, colB, colC FROM tbl
WHERE colA Like '% & form.control & %' ORDER BY colA

I have tried all sorts of combinations and I am not getting anywhere. I've read somewhere about using a parameterized stored procedure. Is that the answer, if so how to I go about creating/using this?

Cheers, Mark.

 

[DangerPowers]

Try setting the whole thing to a variable first, then assign the variable to the rowsource:

strSQL="SELECT colA, colB, colC FROM tbl WHERE colA LIKE '%" & form.control & "%' ORDER BY colA"

 

[mapfax]

Thanks for your help. I had tried this but this didn't work either. My SQL stmt is being pulled from a table which has no quotations. So when trying to reference the control it was just be read as part of the string.

Anyway I explored the parameterized SP and found this successful. I had to call the procedure instead for the rowsouce.

For the procedure I had to assign a local variable to the form control and then concatenate the wild cards, something like:

Create PROCEDURE dbo.Procedure
(@formcontrol varchar(10))
AS
Select @formcontrol = '%' + @formcontrol + '%'
Select ColA, ColB, ColC,
FROM tbl
WHERE ColA LIKE @formcontrol
ORDER BY ColB, ColC