[AccessD] Min Date in a query

William Hindman wdhindman at dejpolsystems.com
Mon Mar 30 14:18:26 CDT 2009


...I'm trying to eliminate the user having to select the next event from a 
combo.

...I have an tblEvents with a DateStart field formatted as Date

...from a query, I need to find the first date in that field greater than 
Date()

...I've tried dozens of variations on Min but they all come back either with 
an aggregate error or return each future event date

...this is of course where'd with a couple other ANDs

INSERT INTO tblExhibitorWeb ( CompanyID, CompanyName, EventShowCode) IN 
'F:\JEMS\XPData\jemdataxp.mdb'
SELECT tblCompany.CompanyID, tblCompany.CompanyName, tblEvents.EventShowCode 
FROM tblEvents INNER JOIN (tblCompany INNER JOIN tblEventReg ON 
tblCompany.CompanyID = tblEventReg.CompanyID) ON tblEvents.EventID = 
tblEventReg.EventID
WHERE (((tblCompany.AdClass)<>"X") AND 
((tblEventReg.ParticipantType)="Exhibitor" Or 
(tblEventReg.ParticipantType)="Exhibitor/NA/PSC") AND 
((tblEventReg.PrimaryContact)=True) AND ((tblEvents.EventName)="JIS") AND 
((Min([tblEvents]![EventStartDate]))>Date()))
ORDER BY tblCompany.CompanyName;

...the error says I can't have and aggregate in the where clause ...help!
...ps I dropped a lot of fields from the sample to shorten it.

William
 





More information about the AccessD mailing list