DateAdd returns incorrect value

[Olle Andreasson]

Hi

I'm trying to calculate StopTime by adding Length in minutes to StartTime
with the DateAdd Function and then compare the calculated time with another
time.

This works fine with all dates and times I've tried except the following.

If DateAdd("n", 20, #7/9/2009 9:30:00 AM#) = #7/9/2009 9:50:00 AM# Then
MsgBox "equal"

The returned value from DateAdd seems to be equal if I look at it visual as a
Date, Double or String but when I compare them in code they are not equal.

Does anyone have a solution for this problem?

/Olle Andreasson

 

[Douglas J. Steele]

Probably a case of floating point round-off error. Date/times are stored as
eight-byte floating point numbers (the integer portion represents the date
as the number of days relative to 30 Dec, 1899 and the decimal portion
represents the time as a fraction of a day). Try using:

If Abs(DateDiff("s", DateAdd("n", 20, [DateValue1]), [DateValue2])) < 1 Then

(In other words, make sure that the difference between them is less than a
second)

 

[John Spencer MVP]

I guess it could be a floating point error since date times are stored as a
double number.

You might try something along the lines of
IF Abs(DateAdd("n", 20, #7/9/2009 9:30:00 AM#) - #7/9/2009 9:50:00 AM#) <
..0000000001 Then

OR using DateDiff function and seeing if there is a difference of less than 1
second

If DateDiff("s",DateAdd("n", 20, #7/9/2009 9:30:00 AM#),#7/9/2009 9:50:00 AM#)
= 0 Then

By the way when I checked
DateAdd("n", 20, #7/9/2009 9:30:00 AM#) - #7/9/2009 9:50:00 AM#
returned 7.27595761418343E-12 (0.000000000007275957614183430) which is not
zero, but it is very close


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Olle Andreasson via AccessMonster.com]

I think I found a solution for the problem.

If I use the FormatDateTime function with the returned value from DateAdd it
seems to work.

FormatDateTime(DateAdd("n", 20, #7/9/2009 9:30:00 AM#))

Could there be any disadvantage with this method?


/Olle Andreasson

 

[Douglas J. Steele]

FormatDateTime, like any other Format function, converts the value to a text
string. That can lead to issues when sorting or comparing for inequality. If
all you're doing is checking if they return the same value, though, you
should be okay.

 

[Douglas J. Steele]

Oh, and if you're comparing the results of the FormatDateTime to simply a
Date field, you're introducing extra type conversion back and forth. (It's
actually never a good idea to rely on default variable coercion when
comparing values: you should always ensure that the conversion is being done
the way you need it to be done)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

FormatDateTime, like any other Format function, converts the value to a
text string. That can lead to issues when sorting or comparing for
inequality. If all you're doing is checking if they return the same value,
though, you should be okay.