Huge date bug?

[PPelle]

Short story: Adding 30 min to 2:00 PM doesn't equal 2:30 PM.

Try this code:

Dim dTemp1 As Date
Dim dTemp2 As Date
 
dTemp1 = #11/21/2013 2:30:00 PM#
dTemp2 = #11/21/2013 2:00:00 PM#
dTemp2 = DateAdd("n", 30, dTemp2)
 
If dTemp1 = dTemp2 Then
    Debug.Print "ok"
Else
    Debug.Print dTemp1 & " <> " & dTemp2
End If
 

Output: 11/21/2013 2:30:00 PM <> 11/21/2013 2:30:00 PM

You can insert these two equal date/time values into at table indexed to not allow duplicates in that field.

I tried creating the dates by using DateSerial and DateAdd, same result.

Adding 1 min to both times (2:01, 2:31) will produce correct output.

I presume floating numbers are playing a role here, but how can this be dealt with?

MS Access 2010, 2003 and 97.

[dgunner71]

Maybe try:

dtTemp1 + .0208333

[30 minutes / 1440 minutes in a day]

Gunner

[PPelle]

Say I have this date in a table, 11/21/2013 2:30:00 PM, calculated with DateAdd. How can I search and find this date? It won't be found. I will have to loop through all the relevant records and use DateDiff to compare each with the search date.

This is not expected behaviour according to this KB: http://support.microsoft.com/kb/210276

[NeoPa]

This is not expected behaviour according to this KB:

Actually, that's exactly what the linked page does predict. Some literal results may not match the calculated results due to Double-Precision rounding errors. That article explains exactly why you are seeing what you are seeing.

To illustrate further try the following code :

Debug.Print CDbl(#11/21/13 14:30#)-CDbl(DateAdd('n', 30, #11/21/13 14:00#))

Not a big number to be sure, but nor is it zero.

[PPelle]

Actually, that's exactly what the linked page does predict.

No, one solution the article recommends is this:

Add an associated date to the time comparison:
var1 = #1/1/99 2:01:00 PM#
var2 = DateAdd("n", 10, var1)
? var2 = #1/1/99 2:11:00 PM#

Subtract 1 min from line two and three, and you will get an False where it should be True. Try yourself in the Immediate window.

[ADezii]

Using a more precise Data Type (Decimal) will give you the OK that you are looking for:

Dim varT1 As Variant
Dim varT2 As Variant
 
 
varT1 = CDec(#11/21/2013 2:30:00 PM#)
varT2 = #11/21/2013 2:00:00 PM#
varT2 = CDec(DateAdd("n", 30, varT2))
 
If varT1 = varT2 Then    'will now show equality
  MsgBox "ok"
Else
  MsgBox varT1 & " <> " & varT2
End If

P.S. - Modifying NeoPa's example in Post# 4 but converting to Decimal, the Expression

Debug.Print CDec(#11/21/2013 2:30:00 PM#) - CDec(DateAdd("n", 30, #11/21/2013 2:00:00 PM#))
 

will evaluate to 0.

[zmbd]

Start out with dtemp1 = dtemp2
use the dateadd to add the 30 minutes to each
then compare.

Sub x()
Dim dTemp1 As Date
Dim dTemp2 As Date
 
dTemp1 = #11/21/2013 2:30:00 PM#
dTemp2 = #11/21/2013 2:00:00 PM#
dTemp1 = DateAdd("n", 30, dTemp1)
dTemp2 = DateAdd("n", 30, dTemp2)
 
If dTemp1 = dTemp2 Then Debug.Print "good"
 
End Sub
 

Need to convert the dates to serial to take a look at them... maybe later... kids in the tub (^_^)

[NeoPa]

@PPelle.

Compare time data

When you compare time values, you may receive inconsistent results because a time value is stored as the fractional part of a double-precision, floating-point number. For example, if you type the following expression in the Immediate window, you receive a false (0) result even though the two time values look the same:

var1 = #2:01:00 PM#
var2 = DateAdd("n", 10, var1)
? var2 = #2:11:00 PM#

When Access converts a time value to a fraction, the calculated result may not be identical to the time value. The small difference caused by the calculation is sufficient to produce a false (0) result when you compare a stored value to a constant value.

Are you sure you want to deny the obvious truth that the article does, indeed, explain exactly what you have reported as a problem. Strangely, the explanation matches the illustration I provided earlier very closely.

Just after this section in the article it gives examples of ways to ensure you don't have this problem. ADezii's solution, though similar to a couple of them, is another technique that would work for you.

Subtract 1 min from line two and three, and you will get an False where it should be True. Try yourself in the Immediate window.

These instructions are too ambiguous for me to be sure I followed them correctly, but :
A) I did follow them as clearly as they are. I found that the result was still True. The exact code I ran was :

var1 = #1/1/99 2:00:00 PM#
var2 = DateAdd("n", 10, var1)
? var2 = #1/1/99 2:10:00 PM#

B) Regardless of the result, this has no bearing on the statement you made denying that the article does warn readers of this known issue when comparing time values.

I'm guessing you didn't even bother to run the line of code I suggested you run. I would expect the results of that line to show clearly why the issue you reported is, in fact, quite predictable.

[dgunner71]

PPelle,

I just created a quick query to test the add function. Because Access reads a date as whole numbers and the time as the decimal portion you can very easily add portions of days by adding times such as .0208.

In query screenshots below, I've created a new value [fxDate] which adds the value I want (i.e. 30 minutes or .0208 of a day). If I only add the value, I will get a mixed number - for this reason, I have to change that mixed number back to a date using the CvDate Function.

You should be able to use this value in your search criteria if you like.

Gunner

[PPelle]

Thanks for all suggestions, I will need some time to try them all and give you proper feedback.