Microsoft Access using in-clause

[Henry Schutte]

I have a form with a control called
[Forms]![frmEmployeeReportsParameters]![txtshiftmachine]. I created a
query that uses this control as part of the in-clause.

SELECT Employees.Shift, tblMachines.MachineName, Employees.FirstName,
Employees.LastName FROM Employees
WHERE (Employees.Shift) In
([Forms]![frmEmployeeReportsParameters]![txtshiftmachine]);

The data I'm trying to send is 1,2. I can use 1 and it works fine.
However, when I use 1,2 the query returns nothing. I read something
about a filter. Can someone explain to me what I need to do to get
this to work?

 

[Marshall Barton]

I have a form with a control called
[Forms]![frmEmployeeReportsParameters]![txtshiftmachine]. I created a
query that uses this control as part of the in-clause.

SELECT Employees.Shift, tblMachines.MachineName, Employees.FirstName,
Employees.LastName FROM Employees
WHERE (Employees.Shift) In
([Forms]![frmEmployeeReportsParameters]![txtshiftmachine]);

The data I'm trying to send is 1,2. I can use 1 and it works fine.
However, when I use 1,2 the query returns nothing. I read something
about a filter. Can someone explain to me what I need to do to get
this to work?

Sorry, you could use a parameter for each value in the list,
but you can not use a parameter for the whole list. (I.e.
SQL syntax elements can not be in a parameter).

To do this, you would have to construct the SQL in code and
use that instead of a saved query.

If you using the query to open a form/report, it's better to
divorce the query from the form by using the OpenForm/Report
method's WhereCondition argument. Base the form/report on a
query that does not include the Where clause, then use code
like this to open it:

strWhere = "Shift IN(" & Me.txtshiftmachine & ")"
DoCmd.OpenReport "thereport", , ,strWhere