Access中短文本类型日期的差值计算及自动增量需求

第一步：修复日期字段类型（核心前提） #

导入后日期字段变成Short Text是DateDiff失效的根本原因，先把文本转成Date/Time类型：

• 先备份数据表，避免数据丢失
• 方法1：直接修改字段类型
  打开表设计视图，把OfficialIssuanceDate、DatePlanSubmitted等字段的类型从Short Text改成Date/Time，如果文本格式标准（比如yyyy-mm-dd、mm/dd/yyyy），Access会自动转换；如果格式不统一弹出错误，改用方法2。
• 方法2：用更新查询转成新字段
  1. 在表设计视图添加新的Date/Time类型字段，比如OfficialIssuanceDate_Date、DatePlanSubmitted_Date
  2. 创建更新查询，执行转换：

     UPDATE 你的表名 
     SET OfficialIssuanceDate_Date = CDate(OfficialIssuanceDate),
         DatePlanSubmitted_Date = IIf(DatePlanSubmitted="", Null, CDate(DatePlanSubmitted)),
         DatePlanCompletedSubmitted_Date = IIf(DatePlanCompletedSubmitted="", Null, CDate(DatePlanCompletedSubmitted));
     

  转换完成后，可把原文本字段改名或删除，用新日期字段替代。

第二步：实现自动日差计算逻辑 #

需要添加两个数字类型字段存储计算结果，比如DaysToPlanSubmit（记录DatePlanSubmitted为空时每日递增的日差）、DaysToCompleteSubmit（同理），再用VBA宏实现每日更新：

1. 添加存储字段 #

打开表设计视图，添加两个Number类型（字段大小选Integer即可）的字段：DaysToPlanSubmit、DaysToCompleteSubmit

2. 编写VBA更新宏 #

按Alt+F11打开VBA编辑器，插入模块，粘贴以下代码：

Sub UpdateDayDifferences()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    ' 替换成你的数据表名称
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM 你的表名")
    
    Do While Not rs.EOF
        ' 更新DaysToPlanSubmit：仅当DatePlanSubmitted为空时计算当日差
        If IsNull(rs!DatePlanSubmitted) Then
            rs.Edit
            rs!DaysToPlanSubmit = DateDiff("d", rs!OfficialIssuanceDate, Date())
            rs.Update
        End If
        
        ' 更新DaysToCompleteSubmit：逻辑同上
        If IsNull(rs!DatePlanCompletedSubmitted) Then
            rs.Edit
            rs!DaysToCompleteSubmit = DateDiff("d", rs!OfficialIssuanceDate, Date())
            rs.Update
        End If
        
        rs.MoveNext
    Loop
    
    ' 清理资源
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

3. 设置每日自动运行 #

• 方式1：Windows任务计划
  创建任务，每天固定时间打开你的Access数据库，同时指定运行这个宏（需在信任中心启用宏）
• 方式2：数据库打开时触发
  如果数据库每天都会被打开，可在窗体的On Open事件或数据库启动事件里调用这个宏，每次打开自动更新一次。

关键注意点 #

• 如果原文本日期格式混乱（比如含非日期字符），需先清理数据再转换，否则CDate会报错
• 一旦DatePlanSubmitted或DatePlanCompletedSubmitted填入日期，宏就会停止更新对应日差字段，保留最终计算值

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