[AccessD] Min Date in a query SOLVED!

AD

...that works, THANK YOU! ...I KNEW there had to be a simple way to do this 
:)
...and my sincere appreciation to all who tried to help me with this.

William

--------------------------------------------------
From: "A.D.Tejpal" <adtp at airtelmail.in>
Sent: Tuesday, March 31, 2009 12:45 AM
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Min Date in a query

> William,
>
>    Sample query given below, gets the first record in table T_A where the 
> value of date field EDate is greater than Date().
>
> ==================================
> SELECT T_A.*
> FROM T_A
> WHERE (((T_A.EDate) In (SELECT TOP 1 EDate FROM T_A  WHERE EDate > Date() 
> ORDER BY EDate)));
> ==================================
>
> Best wishes,
> A.D. Tejpal
> ------------
>
>  ----- Original Message ----- 
>  From: William Hindman
>  To: Access Developers discussion and problem solving
>  Sent: Tuesday, March 31, 2009 00:48
>  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
>