[AccessD] Min Date in a query

...hhhmmm ...actually I might just be able to use the Top 1 ...its very 
"contrived" of course ...use the >Date() in a first qry ...sort desc ...then 
feed that to a Top 1 ...then use that as the criteria in the real qry 
...should work but I was really hoping someone would have a "brilliant" 
moment like gustav's @ :)

William

--------------------------------------------------
From: "William Hindman" <wdhindman at dejpolsystems.com>
Sent: Monday, March 30, 2009 4:08 PM
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Min Date in a query

>
> ...only one event on any given date
> ...no, I've not tried Top 1 ...I need the first date of several after
> today's date
> ...the SQL is copied from the QBE
> ...of course its contrived! ...I wrote it! :)
>
> William
>
> --------------------------------------------------
> From: "Max Wanadoo" <max.wanadoo at gmail.com>
> Sent: Monday, March 30, 2009 3:53 PM
> To: "'Access Developers discussion and problem solving'"
> <accessd at databaseadvisors.com>
> Subject: Re: [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
>>
>> -- 
>> 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
>