By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Huge date bug?

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

Try this code:

Expand|Select|Wrap|Line Numbers
  1. Dim dTemp1 As Date
  2. Dim dTemp2 As Date
  4. dTemp1 = #11/21/2013 2:30:00 PM#
  5. dTemp2 = #11/21/2013 2:00:00 PM#
  6. dTemp2 = DateAdd("n", 30, dTemp2)
  8. If dTemp1 = dTemp2 Then
  9.     Debug.Print "ok"
  10. Else
  11.     Debug.Print dTemp1 & " <> " & dTemp2
  12. 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.
Nov 29 '13 #1
Share this Question
Share on Google+
9 Replies

P: 107
Maybe try:

dtTemp1 + .0208333

[30 minutes / 1440 minutes in a day]

Nov 29 '13 #2

P: 17
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:
Nov 30 '13 #3

Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 30 '13 #4

P: 17
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.
Nov 30 '13 #5

Expert 5K+
P: 8,597
Using a more precise Data Type (Decimal) will give you the OK that you are looking for:
Expand|Select|Wrap|Line Numbers
  1. Dim varT1 As Variant
  2. Dim varT2 As Variant
  5. varT1 = CDec(#11/21/2013 2:30:00 PM#)
  6. varT2 = #11/21/2013 2:00:00 PM#
  7. varT2 = CDec(DateAdd("n", 30, varT2))
  9. If varT1 = varT2 Then    'will now show equality
  10.   MsgBox "ok"
  11. Else
  12.   MsgBox varT1 & " <> " & varT2
  13. End If
P.S. - Modifying NeoPa's example in Post# 4 but converting to Decimal, the Expression
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 30 '13 #6

Expert Mod 5K+
P: 5,287
Start out with dtemp1 = dtemp2
use the dateadd to add the 30 minutes to each
then compare.
Expand|Select|Wrap|Line Numbers
  1. Sub x()
  2. Dim dTemp1 As Date
  3. Dim dTemp2 As Date
  5. dTemp1 = #11/21/2013 2:30:00 PM#
  6. dTemp2 = #11/21/2013 2:00:00 PM#
  7. dTemp1 = DateAdd("n", 30, dTemp1)
  8. dTemp2 = DateAdd("n", 30, dTemp2)
  10. If dTemp1 = dTemp2 Then Debug.Print "good"
  12. End Sub
Need to convert the dates to serial to take a look at them... maybe later... kids in the tub (^_^)
Nov 30 '13 #7

Expert Mod 15k+
P: 31,186
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:
Expand|Select|Wrap|Line Numbers
  1. var1 = #2:01:00 PM#
  2. var2 = DateAdd("n", 10, var1)
  3. ? 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 :
Expand|Select|Wrap|Line Numbers
  1. var1 = #1/1/99 2:00:00 PM#
  2. var2 = DateAdd("n", 10, var1)
  3. ? 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.
Nov 30 '13 #8

P: 107

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.


Attached Images
File Type: png Query1.PNG (3.6 KB, 168 views)
File Type: png Query2.PNG (19.5 KB, 178 views)
Nov 30 '13 #9

P: 17
Thanks for all suggestions, I will need some time to try them all and give you proper feedback.
Nov 30 '13 #10

Post your reply

Sign in to post your reply or Sign up for a free account.