query w multiple criteria returned from function

[ares13]

I have created an application that among other things includes a 'task' list...
In the user maintenance section users are defined and can be added to 'groups'.

When the user opens the app, a listbox shows the tasks assigned to that user, and should also show tasks assigned to any group the user is in.

tblGroupMembers, contains userID and GroupName. A second table tblTasks contains "assigned_to". When a task is created, user selects either a userid or group name to populate "Assigned To"

I have built a function that checks the tblGroups and builds a list of groups the user is in. In the query builder I call a function as the criteria which passes the userid to the query, works fine. When I try to add the function that builds a string of groups, doesnt return any groups.

I have tried using as the criteria In(funcGroups()), where funcGroups returns ("Group1", "Group2"), also tried ('Group1', 'Group2'), also tried "assigned_to=Group1OR assigned_to = Group2"...

if I type Group 1 OR Group2 straight into the query builder, it works, just doesnt work when the function returns the values...

Any comments appreciated

 

[PHV]

Have your function return a string like:
,Group1,Group2,
Then amend the WHERE clause of the SQL code:
SELECT ...
FROM ...
WHERE funcGroups() LIKE '*,' & [your field name] & ',*'
AND ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ares13]

this works perfectly, interestingly after adding this into the querybuilder and running it, it no longer shows in the build view, but is there in the SQL view.

Either way, it works! thank you for the help.