[AccessD] Min Date in a query

William,

    Sample query given below, gets the first record in table T_A where the value of date field EDate is greater than Date().

==================================
SELECT T_A.*
FROM T_A
WHERE (((T_A.EDate) In (SELECT TOP 1 EDate FROM T_A  WHERE EDate > Date() ORDER BY EDate)));
==================================

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: William Hindman 
  To: Access Developers discussion and problem solving 
  Sent: Tuesday, March 31, 2009 00:48
  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