[AccessD] Min Date in a query

..tks Chester but that gives me all of the future date data.

William

--------------------------------------------------
From: "Kaup, Chester" <Chester_Kaup at kindermorgan.com>
Sent: Monday, March 30, 2009 4:20 PM
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Min Date in a query

> Try something like this
>
> 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"))
> GROUP BY tblCompany.CompanyID, tblCompany.CompanyName, 
> tblEvents.EventShowCode
> HAVING (((Min([tblEvents]![EventStartDate]))>Date()))
> ORDER BY tblCompany.CompanyName;
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman
> Sent: Monday, March 30, 2009 2:18 PM
> 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
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>