基于MS Access的SQL查询实现打卡记录周期边界过滤的技术咨询
适配Access的工时表边界过滤SQL实现
原查询失效的核心原因
- 关联逻辑错位:原查询用单条打卡记录的主键
id做子查询关联键,而非员工维度的userid,导致子查询匹配的是记录自身,根本无法实现跨记录的边界过滤,这也是硬编码测试看似正常、切回表单引用就失效的核心原因。 - 边界匹配漏洞:原查询直接用
Between匹配带时间精度的Checktime字段,如果表单的起止日期是纯日期(不带时间),会漏掉结束日当天所有带时间戳的打卡记录,同时无法区分起止日的签入/签出类型边界。 - 过滤规则方向偏差:原代码尝试排除结束日当天的签出记录,但实际需求是排除结束日当天的所有签入记录,且未实现起始日早于节点时间的签出记录过滤逻辑。
可直接运行的查询实现
直接替换原有qrycheckin的SQL内容,无需复杂子查询:
SELECT u.badgenumber, u.name, co.company, c.Checktime, c.Checktype FROM Checkinout AS c INNER JOIN Userinfo AS u ON c.userid = u.userid INNER JOIN Company AS co ON u.company_id = co.company_id WHERE -- 基础范围过滤:规避Between的时间边界漏匹配问题 c.Checktime >= [Forms]![TimeSheet Report]![txtStartDate] AND c.Checktime < DateAdd("d", 1, [Forms]![TimeSheet Report]![txtEndDate]) -- 过滤规则1:剔除统计周期最后一天的所有签入记录 AND NOT ( c.Checktype = 1 AND DateValue(c.Checktime) = DateValue([Forms]![TimeSheet Report]![txtEndDate]) ) -- 过滤规则2:剔除统计周期首日、指定节点时间之前的所有签出记录 AND NOT ( c.Checktype = 0 AND DateValue(c.Checktime) = DateValue([Forms]![TimeSheet Report]![txtStartDate]) AND TimeValue(c.Checktime) < Nz([Forms]![TimeSheet Report]![txtStartTime], 0) ) -- 匹配表单选择的目标公司条件 AND u.company_id = [Forms]![TimeSheet Report]![cboTargetCompany]
实操排坑提示
- 如果表单未设置单独的起始节点时间控件,代码里的
Nz()函数会自动把缺失的起始时间值替换为0点,默认过滤首日零点前的签出记录,无需额外修改代码。 - 对链接的MySQL打卡表,
DateValue/TimeValue/DateAdd函数可正常下推执行,不会因为跨数据库连接失效;测试逻辑时可临时把控件引用替换为硬编码值,注意Access的硬编码日期必须用#包裹,例如DateValue(c.Checktime) = #03/08/2021#,照搬测试平台的双引号写法会触发类型不匹配。 - 相比测试平台用的
NOT EXISTS关联子查询,当前直接对单条记录做判断的逻辑执行效率更高,尤其在打卡表数据量较大的场景下,可避免不必要的全表扫描。
内容的提问来源于stack exchange,提问作者ShadowKing
相关产品推荐
相关产品推荐

