[AccessD] Min Date in a query

And if you put Top 1 into it?

Also, you haven't said what do you do if there are multiple events on the
same future date?

Also, why are you putting them into a table.  Something here you are not
telling us, William.  If the table you are appending to is the source for
the combo, why not just put the query straight into the combo?

Also, to reduce complications, have sub queries which you have tested and
know that work - without filters.  Then use them to build up the query that
you are going to filter on.  Also, don't put Group By "on"

Have to go to bed now.  Eyes are dropping.

Max



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman
Sent: 30 March 2009 22:13
To: Access Developers discussion and problem solving
Subject: Re: [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
> 

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