如何在Microsoft Access查询中比较数值？筛选时长<6小时报错求助

问题背景 #

原查询可正常统计2022-07-01的签到签退时长，但添加时长筛选条件后触发报错：

"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect".

原正常查询语句：

SELECT  (B.Name),
        FORMAT(C.CHECKTIME,'hh') - FORMAT(A.CHECKTIME,'hh') AS TOTAL 
FROM    ( [CHECKINOUT] AS A INNER JOIN [USERINFO] AS B ON A.USERID=B.USERID ) 
                            INNER JOIN [CHECKINOUT] AS C ON B.USERID=C.USERID 
WHERE   A.CHECKTYPE='I' AND C.CHECKTYPE='O' AND 
        FORMAT(A.CHECKTIME,'yyyy-mm-dd') LIKE '%2022-07-01%' AND 
        FORMAT(C.CHECKTIME,'yyyy-mm-dd') LIKE '%2022-07-01%'

错误修改后的语句（问题核心：将筛选条件TOTAL<6错误嵌入SELECT字段别名定义）：

Select  (B.Name),FORMAT(C.CHECKTIME,'hh') - FORMAT(A.CHECKTIME,'hh') AS TOTAL<6  
from(
        [CHECKINOUT] AS A
        INNER JOIN [USERINFO] AS B ON A.USERID=B.USERID  )
        INNER JOIN [CHECKINOUT] AS C ON B.USERID=C.USERID 
where A.CHECKTYPE='I' and C.CHECKTYPE='O' AND FORMAT(A.CHECKTIME,'yyyy-mm-dd') LIKE '%2022-07-01%' AND FORMAT(C.CHECKTIME,'yyyy-mm-dd') LIKE '%2022-07-01%'

报错原因 #

你错误地将筛选逻辑TOTAL<6写在了SELECT子句的字段别名声明中，违反SQL语法规则——SELECT子句仅用于定义字段或计算列的别名，不能直接放置筛选条件。

正确解决方案 #

提供两种可行写法：

方法1：在WHERE子句中直接计算时长并筛选

SELECT  B.Name,
        FORMAT(C.CHECKTIME,'hh') - FORMAT(A.CHECKTIME,'hh') AS TOTAL 
FROM    ( [CHECKINOUT] AS A INNER JOIN [USERINFO] AS B ON A.USERID=B.USERID ) 
                            INNER JOIN [CHECKINOUT] AS C ON B.USERID=C.USERID 
WHERE   A.CHECKTYPE='I' AND C.CHECKTYPE='O' 
        AND FORMAT(A.CHECKTIME,'yyyy-mm-dd') = '2022-07-01'
        AND FORMAT(C.CHECKTIME,'yyyy-mm-dd') = '2022-07-01'
        AND (FORMAT(C.CHECKTIME,'hh') - FORMAT(A.CHECKTIME,'hh')) < 6

注：日期匹配用=比LIKE更高效准确，因为此处日期为固定值，无需模糊匹配。

方法2：用子查询先计算时长，再筛选

如果需要复用TOTAL字段，子查询的写法更清晰：

SELECT  Name, TOTAL
FROM (
    SELECT  B.Name,
            FORMAT(C.CHECKTIME,'hh') - FORMAT(A.CHECKTIME,'hh') AS TOTAL 
    FROM    ( [CHECKINOUT] AS A INNER JOIN [USERINFO] AS B ON A.USERID=B.USERID ) 
                                INNER JOIN [CHECKINOUT] AS C ON B.USERID=C.USERID 
    WHERE   A.CHECKTYPE='I' AND C.CHECKTYPE='O' 
            AND FORMAT(A.CHECKTIME,'yyyy-mm-dd') = '2022-07-01'
            AND FORMAT(C.CHECKTIME,'yyyy-mm-dd') = '2022-07-01'
) AS Temp
WHERE TOTAL < 6

额外优化提示 #

用FORMAT提取小时计算时长存在局限性，若出现跨天签退（当天签到次日签退）会得到负数结果。建议改用DateDiff函数计算更准确的时长：

DateDiff("h", A.CHECKTIME, C.CHECKTIME) AS TOTAL

替换原有的FORMAT计算逻辑后，筛选条件可写为DateDiff("h", A.CHECKTIME, C.CHECKTIME) < 6，能正确处理跨天场景。

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