Using date field in sql query to return a ado recordset

[patman]

The follwing query in Access QBE works:

SELECT *
FROM tblTeachingRooms
WHERE Room_Number in (SELECT Room_Number
FROM tblLessons
WHERE (tblLessons.Period = [Forms]![frmindividualquery]![Period].[Value]
and tblLessons.Date = [Forms]![frmindividualquery]![date1].[Value] );

I used the same query to return a ado recordset in VBA as shown below, but
it returns zero records:
Set rs = CreateObject("ADODB.Recordset")
stSql = "SELECT * FROM tblTeachingRooms"
stSql = stSql & " WHERE Room_Number IN ("
stSql = stSql & "SELECT Room_Number FROM tblLessons "
stSql = stSql & "WHERE (tblLessons.Period = '" &
[Forms]![frmindividualquery]![Period].[Value] & "'"
stSql = stSql & " and [tblLessons].[Date] = #" &
[Forms]![frmindividualquery]![date1].[Value] & "#);"
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

Please help
Thanks