[AccessD] Min Date in a query

> or return each future event date

How do you want to handle multiple events on the same date?

Have you tried Top 1 ?

Have you also posted the query into the QBE form?  This is very contrived.
Why don't you break it out a bit?


Max



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman
Sent: 30 March 2009 20:18
To: Access Developers discussion and problem solving
Subject: [AccessD] Min Date in a query

...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
 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com