时间戳

首页 > 解决方案 > 日期格式不一致的 VBA 插入语句中的 DateAdd 的 MS Access 问题

date - 日期格式不一致的 VBA 插入语句中的 DateAdd 的 MS Access 问题

问题描述

我在如何将日期存储在我的数据库中时遇到问题。

我需要创建一个临时 TableC，其中将为 TableA 上的每条记录插入 X 记录。其中 X 是周数。生成的表将在 UNION 语句中与其他几个查询一起使用。

问题在于 DateAdd 如何为每条新记录创建日期。日期最初以 dd/mm/yyyy 格式存储。但是生成的 DateAdd("ww", i, rst![Date]) 有时会以 mm/dd/yyyy 格式存储，从而在 30K 结果行中造成严重破坏。

编写了一个 VB Sub 来创建表，下面是一个示例，说明它的实际存储方式与预期方式。

 



Sub AddItems()
    Dim db as DAO.Database
    Dim rst as DAO.Recordset
    Dim Sql1, Sql2 as String

    Sql1 = "SELECT [a].*, [b].[Date], [b].[Weeks]. [b].[Rate] FROM TableA as [a] LEFT JOIN TableB as [b] WHERE [a].[GroupId] = [b].[Id] ORDER BY [b].[Date], [a].[Id];"

    Set db = CurrentDb
    Set rst = db.OpenRecordset(Sql1)

    rst.movefirst
    While NOT rst.EOF
        If rst![Weeks] > 0 Then
            For i = 1 to rst![Weeks]
                Sql2 = "INSERT INTO TableC ([ID], [CUSTOMER], [DATE], [AMOUNT]) VALUES ("
                Sql2 = Sql2 & rst![ID] & ", " & rst![CUSTOMER]
                Sql2 = Sql2 & "#" & Format(DateAdd("ww", (i - 1), Format(rst![Date], "mm/dd/yyyy")), "mm/dd/yyyy") & "#"
                Sql2 = Sql2 & ", " & rst![AMOUNT]
                Sql2 = Sql2 & ")"
                Debug.Print Format(DateAdd("ww", (i - 1), Format(rst![Date], "mm/dd/yyyy")), "mm/dd/yyyy")
                db.Execute(Sql2)
            Next i
        End If
        rst.movenext
    Wend
End Sub  






RESULTING TABLE SAMPLE
+-------------+------------+------------+
|    Week     |  On Table  |  On Debug  |   Expected Data Inserted
+-------------+------------+------------+
|      1      | 12/02/2019 | 02/12/2019 |  Should be 2 / Dec / 2019
|             | as 12/Feb  |  Correct   |
+-------------+------------+------------+
|      2      | 12/09/2019 | 09/12/2019 |  Should be 9 / Dec / 2019
|             | as 12/Sept |  Correct   |
+-------------+------------+------------+
|      3      | 16/12/2019 | 16/12/2019 |  Should be 16 / Dec / 2019
|             |  Correct   |  Correct   |
+-------------+------------+------------+

立即窗口上打印的结果是正确的，但在实际表格中，信息是不正确的。我的示例数据从 2019 年 2 月 12 日开始（2019 年 12 月 2 日，通过 TableA 中表格视图上的日期选择器确认）

调试窗口显示要存储的正确信息，但在表上是不正确的，即使 TableC 中的字段 [DATE] 的格式为“短日期”和 IsDate 验证规则。

如果没有这两个 Format 语句，结果与预期的结果大相径庭。然而，生成的 DATE 字段并不一致。

有没有办法根据系统的设置一致地生成日期并存储它们？

标签： datems-accesssql-insertdateadd

解决方案

如果数据类型为Date，则日期值不以格式存储。格式仅用于显示或在 SQL 中连接时。所以试试：

Sub AddItems()

    Dim db  As DAO.Database
    Dim rst As DAO.Recordset
    Dim Sql1 As String
    Dim Sql2 As String

    Sql1 = "SELECT [a].*, [b].[Date], [b].[Weeks]. [b].[Rate] FROM TableA as [a] LEFT JOIN TableB as [b] WHERE [a].[GroupId] = [b].[Id] ORDER BY [b].[Date], [a].[Id];"

    Set db = CurrentDb
    Set rst = db.OpenRecordset(Sql1)

    rst.MoveFirst
    While Not rst.EOF
        If rst![Weeks] > 0 Then
            For i = 1 to rst![Weeks]
                Sql2 = "INSERT INTO TableC ([ID], [CUSTOMER], [DATE], [AMOUNT]) VALUES ("
                Sql2 = Sql2 & rst![ID] & ", '" & rst![CUSTOMER] & "', "
                Sql2 = Sql2 & "#" & Format(DateAdd("ww", i - 1, rst![Date]), "mm/dd/yyyy") & "#, "
                Sql2 = Sql2 & Str(rst![AMOUNT])
                Sql2 = Sql2 & ")"
                Debug.Print Format(DateAdd("ww", i - 1, rst![Date]), "mm/dd/yyyy")
                db.Execute(Sql2)
            Next i
        End If
        rst.MoveNext
    Wend
End Sub

也就是说，为 TableC 打开第二个记录集，然后使用AddNew和Update来附加几周的记录会更简单、更快捷。

如果您的“日期”存储为text，则除了手动编辑以使其格式一致之外，没有其他方法可以解决您的问题。

推荐阅读