Hi All
I am trying to create a filter criteria in a query from a string, stored in a table. I'm doing it this way because the filter criteria needs to be dynamic as different users will have different skilltags.
Example for the filter criteria would be as follows"P1" Or ("P2" Or "P3" Or "P4" Or "A1" Or "A2")This would work if it were manually pasted into the query design view and the query then run. But when I try to reference the table for it to pick this up, I get no results. (Yes, there is test data there that should provide a hit)
Tried referencing by using the expression builder and the dlookup function, unsuccessful on both counts.
SQL code reads as follows
Code:
SELECT [TPS 2].ID, [TPS 2].[Benefit Type], Left(PlainText([Issue Comments]),50) AS txfldFROM [TPS 2]WHERE ((([TPS 2].[Benefit Type])=[Session]![ssfilter]) AND (([TPS 2].[Action taken / Resolution])="Referred to helpdesk") AND (([TPS 2].[Next action])="Send to helpdesk"));
or
Code:
SELECT [TPS 2].ID, [TPS 2].[Benefit Type], Left(PlainText([Issue Comments]),50) AS txfldFROM [TPS 2]WHERE ((([TPS 2].[Benefit Type])=DLookUp("[ssfilter]","session","[Session id]=" & 1)) AND (([TPS 2].[Action taken / Resolution])="Referred to helpdesk") AND (([TPS 2].[Next action])="Send to hepdesk"));
Would appreciate a suggestion on how to make this work, or another approach that would provide this sort of functionality. Thanks in advance