VBA中的时间比较：同一时间不相等 [英] Time comparison in VBA: inequal while same time

问题描述

我正在将数据库从Microsoft Access移到SQL Server，并正在验证远程表和本地表之间的数据相等性。这样做时，我在比较时间字段时遇到了一个奇怪的问题。

I'm moving a database from Microsoft Access to SQL server, and am validating data equality between the remote and the local table. While doing so, I encountered an oddity when comparing a time field.

我正在使用以下函数：

Public Function CheckAllFieldsEqual(rs1 As DAO.Recordset, rs2 As DAO.Recordset) As Boolean
    Dim fld As DAO.Field
    CheckAllFieldsEqual = True
    For Each fld In rs1.Fields
        If fld.Value <> rs2.Fields(fld.NAME).Value Then GoTo ReturnFalse
    Next fld
    Exit Function
ReturnFalse:
    Debug.Print "Fields inequal! " & fld.NAME & ": " & fld.Value & " - "; rs2.Fields(fld.NAME).Value
    CheckAllFieldsEqual = False
    MsgBox "Inequal!", vbCritical
    Stop
End Function

rs1 是远程记录集，设置为随机行。 rs2 是本地变量，设置为具有相同主键的行。它们应该是相同的，因为我刚刚通过使用 DoCmd.TransferDatabase

rs1 is the remote recordset, set to a random row. rs2 is the local variant, set to a row with the same primary key. They should be identical, since I just created the remote table by moving the local table using DoCmd.TransferDatabase

Debug.Print fld.Value 返回09:46:00。 Debug.Print rs2.Fields（fld.NAME）.Value 也返回09:46:00。但是，比较不会通过。

Debug.Print fld.Value returns 09:46:00. Debug.Print rs2.Fields(fld.NAME).Value also returns 09:46:00. However, the comparison doesn't pass.

奇怪的部分：

Debug .Print CDbl（fld.Value）返回0.406944444444444

Debug.Print CDbl(fld.Value) returns 0.406944444444444

Debug.Print CDbl（rs2.Fields（fld.NAME ）.Value）返回0.406944444444445

Debug.Print CDbl(rs2.Fields(fld.NAME).Value) returns 0.406944444444445

如何避免此类错误？我应该添加类型检查并以一定的精度检查时间字段的相等性，还是有一种更简单的方法来避免这种情况？

How do I avoid these kinds of errors? Should I add type checking and check equality for time fields with a certain precision, or is there a more simple way to avoid this?

推荐答案

使用 DateDiff 。正是出于这样的目的而忽略了浮点错误：

Use DateDiff. It is exactly for a purpose like this to ignore the floating point errors:

If DateDiff("s", fld.Value, rs2.Fields(fld.NAME).Value) <> 0 Then GoTo ReturnFalse

这篇关于VBA中的时间比较：同一时间不相等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！