Using IN() In Query Criteria with Form Control

[txgeekgirl]

I am trying to reference a Form Control for an IN() Criteria in a Query.

In the VBA - I wrapped txt quotes around the items in the list and saved to a form control tb.Filter - seperated by no space comma - trimming last comma.

  strFilter = ""
    'get all StaffIDS
    For Each var In Me.lbStaff.ItemsSelected
        strFilter = strFilter & "'" & Me.lbStaff.Column(0, var) & "',"
    Next var
 
    Me.tbFilter = Mid(strFilter, 1, Len(strFilter) - 1)

In the Query - I need it to append only items where StaffID IN List... not recognizing any criteria and appends zero items. Any hints??? :banghead:

INSERT INTO GetSumTbl ( LineItem, LineDesc, PayCode, Quantity )
SELECT EmpQ.StaffId, EmpQ.EmpName, [Pay Codes].PayCode, 0 AS MyQuantity
FROM EmpQ, [Pay Codes]
WHERE (((EmpQ.StaffId) In ([Forms]![Accounting Report]![tbFilter])))
ORDER BY EmpQ.StaffId;

 

[liddlem]

I may be wrong, but I think your problem is the last exclamation mark in . . .
WHERE (((EmpQ.StaffId) In ([Forms]![Accounting Report]![tbFilter])))

Should it be a full stop? eg . . . . ([Forms]![Accounting Report].[tbFilter])))

 

[txgeekgirl]

Nope changing the "!" to a "." doesn't work.

 

[txgeekgirl]

The answer - thanks to my boss who had 15 min - quad quotes

For Each var In Me.lbStaff.ItemsSelected
        strFilter = strFilter & """" & Me.lbStaff.Column(0, var) & """" & ","
    Next var
    Me.tbFilter = Mid(strFilter, 1, Len(strFilter) - 1)