Null in subquery returns no records
From: Mike MacSween <mike.macsween.nospam_at_btinternet.com>
Date: Fri, 6 Feb 2004 13:36:26 -0000
Message-ID: <4023985a$0$4096$5a6aecb4_at_news.aaisp.net.uk>
This as the row source for a combo:
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;
Date: Fri, 6 Feb 2004 13:36:26 -0000
Message-ID: <4023985a$0$4096$5a6aecb4_at_news.aaisp.net.uk>
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 - 14:36:26 CET