William Hindman
wdhindman at dejpolsystems.com
Mon Mar 30 15:08:18 CDT 2009
...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 >