Putting an aggregate inside a where clause

[ashley25]

I need to change the below to a where clause to fit inside a union query that is just where clauses, can someone offer some advice?

tblNEWNONTODATA.DateOfVisit) AS FirstOfDateOfVisit
FROM tblNEWNONTODATA
GROUP BY tblNEWNONTODATA.EVX, tblNEWNONTODATA.TCGDecision
HAVING (((tblNEWNONTODATA.TCGDecision)="Adopted" Or (tblNEWNONTODATA.TCGDecision)="Hot Tasked") AND ((First(tblNEWNONTODATA.DateOfVisit))>=#10/1/2014#));

 

[pbaldy]

Not sure what you mean by "just where clauses"; each select within a union query stands alone, so that could stay as is. You may want Min rather than First by the way.

 

[namliam]

Anything in the having can be moved to the where clause as long as there is no aggregate function involved... Like First, Min or Max etc all HAVE TO BE in the having clause unless you make it into a (less efficient) subselect.

Your query should work within a union as is though, like Paul already said, so why bother?