[AccessD] Min Date in a query

You will probably have to put in all of those pesky Access parenthesis. They
make it so hard to read.

First of all, test this:  SELECT TOP 1 EventID, MIN(EventStartDate) FROM
tblEvents WHERE EventStartDate > DATE() AND EventName = "JIS"  GROUP BY
EventID

 That should give you one The next Event ID

If that works, try this:

SELECT
    tblEvents.EventShowCode
FROM tblEvents
    INNER JOIN (SELECT TOP 1 EventID, MIN(EventStartDate) FROM tblEvents
WHERE EventStartDate > DATE() AND     tblEvents.EventName = "JIS"  GROUP BY
EventID) C
        ON tblEvents.EventID = C.EventID

If that looks good, try it as such:


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() AND     tblEvents.EventName = "JIS"  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
ORDER BY tblCompany.CompanyName;


HTH
David

On Mon, Mar 30, 2009 at 2:44 PM, William Hindman <
wdhindman at dejpolsystems.com> wrote:

> 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
>