[AccessD] Min Date in a query

Sorry, what I meant was not "contrived" but "complicated".

What I normally do is these situations is to create a query fetching all the
data that I am interested in.
Once that is behaving ok, then I put in the criteria (ie, under the
CompanyID column I would enter [Forms]![MyForm]![CompanyID].
I would then have the query linked to and filtered by the value in the Form.
Then on the Form, I would set the record source = thisquery.
If I wasn't populating a form, then, in code, I would use the filtered query
as the recordsource to create an append sql

HTH

Max



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