ignore conditions in a query

[diwin]

Can I put a condition on these conditions???

The red conditions below only apply when there is 1 or more records in the associated table ( tblPickYearSeason or tblPickBugStage). If either or both of the named tables is/are empty, the condition(s) should be ignored and allow all records to be tested against the remaining condition(s). Can I do that?

SELECT DISTINCT tblPointInfo.MainPointID AS ID, tblPointInfo.Point, tblPointInfo.PointType, tblSiteInfo.SiteID, tblPointInfo.YearID, tblPointInfo.SeasonID, tblBugInfo.BugID, tblBugLifeStageInfo.f_BugStageID
FROM ((tblBugLifeStageInfo 
INNER JOIN tblBugInfo ON tblBugLifeStageInfo.f_Bug = tblBugInfo.ID) 
INNER JOIN tblPointInfo ON tblBugInfo.f_Point = tblPointInfo.ID) 
INNER JOIN tblSiteInfo ON tblPointInfo.f_SiteID = tblSiteInfo.ID
WHERE (((tblSiteInfo.SiteID)=[Forms]![frmViewInfo].[txtTest]) 
AND [red]((tblPointInfo.YearID) In (SELECT f_YearID FROM tblPickYearSeason ))[/red] 
AND [red]((tblPointInfo.SeasonID) In (SELECT f_SeasonID FROM tblPickYearSeason ))[/red] 
AND [red]((tblBugInfo.BugID) In (SELECT f_BugID FROM tblPickBugStage ))[/red] 
AND [red]((tblBugLifeStageInfo.f_BugStageID) In (SELECT f_StageID FROM tblPickBugStage ))[/red]);

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[Remou]

Try:

WHERE tblSiteInfo.SiteID=[Forms]![frmViewInfo].[txtTest]
AND (tblPointInfo.YearID In 
      (SELECT f_YearID 
       FROM tblPickYearSeason) OR Dcount("*","tblPickYearSeason")=0)
AND (tblPointInfo.SeasonID In 
      (SELECT f_SeasonID 
       FROM tblPickYearSeason) OR Dcount("*","tblPickYearSeason")=0)
AND (tblBugInfo.BugID In 
      (SELECT f_BugID 
       FROM tblPickBugStage) OR Dcount("*","tblPickBugStage")=0)
AND  (tblBugLifeStageInfo.f_BugStageID In 
      (SELECT f_StageID 
       FROM tblPickBugStage)  OR Dcount("*","tblPickBugStage")=0)

(It all sounds pretty interesting)

http://lessthandot.com