query using list of values from a field in another table

[neiljor]

I have a database with two tables. One is tblUsername and the other is tblTasks.

tblUsername contains two fields - username and teams. Teams is a list of teams that the user should be allowed to see.

tblTasks contains a taskID, taskname ... and teamdetails.

I am trying to write a query that will return a list of all records in the tblTasks table where teamdetails is listed in the record for that user in tblUsername.

If I have only one entry in tblUsername.teams, eg. Archer, the following query works:

SELECT TblTasks.TaskID, TblTasks.Group, TblTasks.Team, TblTasks.TeamDetails, TblTasks.DateReceived, TblTasks.DateStarted
FROM TblTasks, qryWhoAmI
WHERE (((TblTasks.TeamDetails) In ([qryWhoAmI]![teams])))
ORDER BY TblTasks.DateStarted DESC , TblTasks.Team;

where qryWhoAmI is currently used to get the user to enter their username. (This will be replaced later).

If I change the data in the tblUsername.teams field to Archer, SID or “Archer”, “SID” or Archer;SID the query doesn’t work. Is it possible/ wise to do this? Or is there a better way?

Please help.
Thanks.

 

[irini]

Try using this in place of your current where statement:
WHERE (((TblTasks.TeamDetails) like "*" & ([qryWhoAmI]![teams]) & "*"))