MS Access 嵌套IIF参数查询语法错误排查：计算月末在库项目总量

现有SQL的语法错误 #

• 聚合函数嵌套错误：Access SQL不支持SUM()函数嵌套使用，你写的三层嵌套SUM属于非法语法，执行时会直接抛出「聚合函数不能嵌套」的报错。
• 空值判断语法错误：or is null前未指定判断字段，正确写法应为[dtLegalEnd] IS NULL，原有写法会被数据库判定为语法不合法。
• 统计逻辑完全反向：你的需求是统计月末处于有效阶段的项目，满足条件时应该计数为1，但你写的IIF逻辑是满足条件返回0，完全不符合统计要求。
• 语法冗余：不需要多层IIF嵌套，单条WHERE条件即可完成所有规则判断，执行效率也更高。

更优实现方案 #

方案1：支持动态指定统计月份的参数查询 #

不需要用户手动输入月末日期，输入年月即可自动计算当月最后一天做统计：

PARAMETERS [Enter Statistic Year (e.g. 2024)] Short, [Enter Statistic Month (e.g. 6)] Short;
SELECT COUNT(*) AS MonthEndProjectStock
FROM tblProjectsA
WHERE 
    -- 统计月月末前已创建
    dtCreate <= DateSerial([Enter Statistic Year], [Enter Statistic Month]+1, 0)
    -- 未到立项结束时间/结束时间未填
    AND (dtLegalEnd > DateSerial([Enter Statistic Year], [Enter Statistic Month]+1, 0) OR dtLegalEnd IS NULL)
    -- 未最终关闭/关闭时间未填
    AND (dtFinalClosed > DateSerial([Enter Statistic Year], [Enter Statistic Month]+1, 0) OR dtFinalClosed IS NULL);

方案2：一次性生成所有年月的统计结果 #

先新建一个辅助日历表tblCalendar，仅需statMonth字段，存入所有需要统计的年月首日（比如2023-01-01、2023-02-01），执行以下SQL即可一次性输出全量历史月份的统计结果：

SELECT 
    Format(statMonth, "yyyy-mm") AS statisticMonth,
    COUNT(tblProjectsA.id) AS MonthEndProjectStock
FROM tblCalendar
LEFT JOIN tblProjectsA ON
    dtCreate <= DateSerial(Year(statMonth), Month(statMonth)+1, 0)
    AND (dtLegalEnd > DateSerial(Year(statMonth), Month(statMonth)+1, 0) OR dtLegalEnd IS NULL)
    AND (dtFinalClosed > DateSerial(Year(statMonth), Month(statMonth)+1, 0) OR dtFinalClosed IS NULL)
GROUP BY Format(statMonth, "yyyy-mm")
ORDER BY statisticMonth;

内容的提问来源于stack exchange，提问作者greenarrowgem