At most one record can be returned by this subquery

[Spocks]

Hi all. I'm trying to return a list of students in a particular team who have not attended a certain number of sessions at a gym, between two dates. For some reason i get the error. At most one record can be returned by this subquery. (Error 3354).
I don't understand as i have the LIKE in there it should work fine but it does not. Thanks in advance.

SELECT [Student ID], Count(*) AS ["Number of Times Attended"]
FROM Gym_Attendance
WHERE [Gym Date] BETWEEN [Forms]![SV_Attendance]![txtStart].Value AND [Forms]![SV_Attendance]![txtEnd].Value
AND [Student ID] Like
(SELECT [Student ID]
FROM Student_Sports
WHERE [Sporting Team] = [Forms]![SV_Attendance]![lstTeam].Value)
GROUP BY [Student ID]
HAVING Count(*) < [Forms]![SV_Attendance]![txtNo].Value;

 

[MarkK]

LIKE compares one thing to one other thing, so your sub-query must return one thing. Maybe you mean to use IN?

WHERE StudentID IN
  ( 
    SELECT StudentID
    FROM tblSports
    WHERE TeamName = 'Bears'
  )