[AccessD] Min Date in a query

David

...syntax error (missing operator) in query expression 'tblEvents.EventID 
=tblEventReg.EventID
INNER JOIN tblCompany ON tblCompany.CompanyID = tblEventReg.CompanyID
INNER JOIN (SELECT TOP 1 EventID, MIN(EventStartDate) FROM tblEvents
WHERE EventStartDate > DATE() GROUP BY EventID) C'

...I've looked without finding anything?

William

--------------------------------------------------
From: "David McAfee" <davidmcafee at gmail.com>
Sent: Monday, March 30, 2009 4:17 PM
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Min Date in a query

> SELECT
>    tblCompany.CompanyID,
>    tblCompany.CompanyName,
>    tblEvents.EventShowCode
> FROM tblEvents
>    INNER JOIN tblEventReg
>        ON tblEvents.EventID =tblEventReg.EventID
>    INNER JOIN tblCompany
>        ON tblCompany.CompanyID = tblEventReg.CompanyID
>    INNER JOIN (SELECT TOP 1 EventID, MIN(EventStartDate) FROM tblEvents
> WHERE EventStartDate > DATE() GROUP BY EventID) C
>        ON tblEvents.EventID = C.EventID
> WHERE
>    tblCompany.AdClass <>"X" AND
>    tblEventReg.ParticipantType IN ("Exhibitor", "Exhibitor/NA/PSC") AND
>    tblEventReg.PrimaryContact = True AND
>    tblEvents.EventName = "JIS"
> ORDER BY tblCompany.CompanyName;
>