Parameterised IN (SELECT ...

[asearle]

Hi everyone,

I have a query which is reduced by a "IN (SELECT ..." clause entered as a filter on a specific column.



This works fine but what I would like to do now is add a parameter to the SELECT clause. For example ...

IN ("SELECT myPersNo FROM tbl_Personnel WHERE Dept_No=" & Forms!frm_Dept!Dept_No )

As soon as I do this the filter stops working because (I assume) it is handling the SELECT clause as a string.

I have a work-round (adding the table tbl_Dept to my query) but I'm very interested to know whether "IN (SELECT ..." clauses (when used as filters) can incorporate variable parameters?

Any help with this would be gratefully received.

Regards,
Alan

[pbaldy]

If this is just a query (not in VBA code), does this work?

IN (SELECT myPersNo FROM tbl_Personnel WHERE Dept_No = Forms!frm_Dept!Dept_No)