Time comparisons in Access 2003 - off!

  • Thread starter Thread starter Paul W
  • Start date Start date
P

Paul W

I have a table that stores times in a datetime field, eg." 7:00 am". On my
XP machine (access 2003 sp1), if I do a query for "where starttime = #7:00
am#" I get records returned. If I do the same on another Windows 2003
(access 2003 no SP) machine, with the exact same table, I get no records! If
I calculate the difference between 'starttime' and #7:00 am# I get a very
small number (~10E-17).

I know that you can expect 'rounding' errors to cause problems when doing
calcs/comparisons on datetimes because they are stored as decimals. But I
would expect the behavior to be consistent across machines.

Anybody know what's going on here?

Paul.
 
Paul, this is almost certainly the rounding errors inherent in floating
point calculations, as you seem to be aware.

Assuming that this is actually the same mdb file, the difference in behavior
is probably the result of different versions of JET (the query engine in
Access). To test that idea, locate msjet40.dll on both machines (typically
in \windows\system32), right-click and choose Properties, and look on the
Version tab.

The most efficient way to write that query might be something like this:
WHERE (MyDate > #6:59:59#) AND (MyDate < #7:00:01#)
that allows JET to use any index on the field, but it could return values
that were intended to be stored as 6:59:59 or 7:00:01 (and are off by
rounding errors). If that's an issue and we are talking time values only (no
dates, and esp no negative dates) you may be able to use something like:
Abs(CDbl(MyDate) - 0.291666666666667) < 0.00000000000001
 
Back
Top