Null in subquery returns no records

This as the row source for a combo:

SELECT qryRole.RoleID, qryRole.Role
FROM qryRole
WHERE (((qryRole.RoleID) Not In (SELECT RoleID FROM qryRoleEvent INNER JOIN qryEvent ON qryRoleEvent.EventID = qryEvent.EventID WHERE ProdID = Forms!frmProductions!ProdID)))
ORDER BY qryRole.Role;

If there is just one RoleID with a null value in the subquery then the main query returns no records atall.

This is needed:

SELECT qryRole.RoleID, qryRole.Role
FROM qryRole
WHERE (((qryRole.RoleID) Not In (SELECT RoleID FROM qryRoleEvent INNER JOIN qryEvent ON qryRoleEvent.EventID = qryEvent.EventID WHERE ProdID = Forms!frmProductions!ProdID AND RoleID IS NOT NULL))) ORDER BY qryRole.Role;

Why?

Yours, Mike MacSween Received on Fri Feb 06 2004 - 07:36:26 CST