Select second max Access table

Question

The following code is meant to return all project names for the second most recent date of all dates in the table. I however keep getting the error "Your query does not include the specified expression 'Project Name' as part of an aggregate function. What am I doing incorrectly?

SELECT DISTINCT TOP 2 Max([Report Date]) AS MaxReportDate FROM RedProjectHistorical WHERE (((RedProjectHistorical.[Report Date]) Not In (Select Max([Report Date]) FROM RedProjectHistorical)));

Answer 1

Try with the simpler:

SELECT DISTINCT TOP 2 
    [Report Date] AS MaxReportDate
FROM 
    RedProjectHistorical
WHERE 
    [Report Date] Not In 
        (SELECT Max(T.[Report Date]) FROM RedProjectHistorical As T)
ORDER BY
    [Report Date] Desc;