2

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.

I'm using the following function:

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 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 returns 09:46:00. Debug.Print rs2.Fields(fld.NAME).Value also returns 09:46:00. However, the comparison doesn't pass.

The odd part:

Debug.Print CDbl(fld.Value) returns 0.406944444444444

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?

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • 1
    I think this is just a feature of floating point numbers. You must set up some precision, let say to to milliseconds. Just search the web for "comparing floating point numbers", there is number of articles about it. – MarcinSzaleniec Dec 13 '17 at 09:55
  • 1
    Just simple information: `0.000000012` is 1 millisecond. – Kazimierz Jawor Dec 13 '17 at 10:05
  • Both fields have the same data source, with minute-level information. There shouldn't be any milliseconds present, let alone a difference of a tiny fraction of a millisecond. But I guess type checking and specifying precision if I'm comparing dates can't be avoided, then? – Erik A Dec 13 '17 at 10:08
  • @ErikA: Fine to add the `[ms-access]` tag, and the `[vba]` tag is OK, but what's wrong with the `[access-vba]` tag? It is only `[excel-vba]` that for some reason has been labeled "deprecated". I've noticed that user braX and a python moderator driven by some divine force are having their time retagging hundreds of (old) questions, but I do count you as one who instantly can spot Access VBA. So I wonder, what's the benefit from this reduction in search options, please? – Gustav Jul 08 '20 at 18:24
  • @Gustav The decision has been made, see [this](https://meta.stackoverflow.com/a/399177/7296893). Apparently, all vba tags are to be removed, and access-vba is the only one left, solely based on the effort it would take to remove it. I've decided that fighting this change is futile, so best to get it over with asap, and I've started with my own questions, and the ones that are problematic for systematic retagging due to using an excessive amount of tags. – Erik A Jul 08 '20 at 18:29
  • @ErikA: OK, I see. I'll take your word for it. I don't agree in this, indeed not in the total lack of communication from moderators, but my time is not for a lengthy discussion. Thanks for the link. – Gustav Jul 08 '20 at 18:48

1 Answers1

2

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
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Unfortunately, it's not that simple, since this function is being used to process multiple types of fields, so I guess I need to create a separate function to do the comparison and check the field type first. But thanks for the advice, since I can use DateDiff there. – Erik A Dec 13 '17 at 10:40
  • Or, if you elsewhere compare Double, compare to an interval as suggested. The error is a _multifactor_ below 1 ms. – Gustav Dec 13 '17 at 10:46