DateTime Access bug?

I wrote a routine for somebody yesterday. When I compare datStart to
datEnd in the DoWhile comamnd, when the date/times match they don't.
For example, 1:00:00 PM does not match 1:00:00 PM.

I guess this may be due to precision, or imprecision, of the value
stored in a date field. IOW, date/time fields are accurate to the
second but may or may not be accurate if you add or subtract some time
from it.

If you have an idea why the values, when exactly the same, equate to not
being the same, I'd like to hear the explanation...is it due to doubles
values not being precise, tuncation of doubles?

You can cut this code out and run it to see what I mean. I present a
msgbox at the end.

Public Sub CH()

Dim datStart As Date
Dim datEnd As Date
Dim intHours As Integer
Dim intMinutes As Integer
Dim strMsg As String

'change hours increment here to test
intHours = 3
datStart = Now()

strMsg = "Now " & datStart & vbNewLine & _
"Hours " & intHours & vbNewLine

'add the number hours to it to create the end time
datEnd = DateAdd("h", intHours, datStart)

'remove seconds from start/end time
'if the time is 1:23:45 it will be 1:23:00
datStart = DateAdd("s", Second(datStart) * -1, datStart)
datEnd = DateAdd("s", Second(datEnd) * -1, datEnd)

'get number of minutes from start time to next hour
'Ex: start at 5:30, 30 minutes till 6:00
intMinutes = 60 - Minute(datStart)

If intMinutes <> 0 Then
'add those minutes to get the next next hour
'ex: start at 5:30, add 30 so starttime now is 6:00
datStart = DateAdd("n", intMinutes, datStart)
End If

'now get the ending minutes
intMinutes = Minute(datEnd)
If intMinutes <> 0 Then
'bring the ending hour to 0 minutes, 0 seconds.
'ex: punch out at 7:30, send to 7:00
datEnd = DateAdd("n", intMinutes * -1, datEnd)
End If

strMsg = strMsg & "Start " & datStart & vbNewLine & _
"End " & datEnd & vbNewLine

intHours = 0

'this DoWhile adds an extra hour to the result. When the
'values match 6/3/2004 12:00 PM to 6/3/2004 12:00 PM they
'don't match
Do While datStart < datEnd

'This DoWhile works correctly by forcing a comparison based on
'formating the dates. Comment out either DoWhile to see
'the difference.
'Do While Format(datStart, "mm/dd/yyyy hh:nn") <> _
Format(datEnd, "mm/dd/yyyy hh:nn")

intHours = intHours + 1
datStart = DateAdd("h", 1, datStart)
Loop

MsgBox strMsg & "Tot Hours Between " & intHours

End Sub

re: DateTime Access bug?

Date/time data is stored as a double (although it does not accept as large a
range of numbers as a double). Because it is actually a floating point
number in which the decimal fraction represents the time of day you would
expect floating point inaccuracies to creep in.

As a simple example in the debug window type:
?cdate(0.499996), cdate(0.5), cdate(0.499996) = cdate(0.5)

and hit the enter key, you'll get
12:00:00 12:00:00 False

so the time representation is the same but the underlying value is
different.

You can always go for a test such as

?cdate(0.499996) < dateadd("s",1, cdate(0.5)) and cdate(0.499996) >
dateadd("s",-1, cdate(0.5))

Which would return true

--
Terry Kreft
MVP Microsoft Access


"Salad" <oil@vinegar.com> wrote in message
news:vEHvc.20334$Tn6.8594@newsread1.news.pas.earth link.net...[color=blue]
> I wrote a routine for somebody yesterday. When I compare datStart to
> datEnd in the DoWhile comamnd, when the date/times match they don't.
> For example, 1:00:00 PM does not match 1:00:00 PM.
>
> I guess this may be due to precision, or imprecision, of the value
> stored in a date field. IOW, date/time fields are accurate to the
> second but may or may not be accurate if you add or subtract some time
> from it.
>
> If you have an idea why the values, when exactly the same, equate to not
> being the same, I'd like to hear the explanation...is it due to doubles
> values not being precise, tuncation of doubles?
>
> You can cut this code out and run it to see what I mean. I present a
> msgbox at the end.
>
> Public Sub CH()
>
> Dim datStart As Date
> Dim datEnd As Date
> Dim intHours As Integer
> Dim intMinutes As Integer
> Dim strMsg As String
>
> 'change hours increment here to test
> intHours = 3
> datStart = Now()
>
> strMsg = "Now " & datStart & vbNewLine & _
> "Hours " & intHours & vbNewLine
>
> 'add the number hours to it to create the end time
> datEnd = DateAdd("h", intHours, datStart)
>
> 'remove seconds from start/end time
> 'if the time is 1:23:45 it will be 1:23:00
> datStart = DateAdd("s", Second(datStart) * -1, datStart)
> datEnd = DateAdd("s", Second(datEnd) * -1, datEnd)
>
> 'get number of minutes from start time to next hour
> 'Ex: start at 5:30, 30 minutes till 6:00
> intMinutes = 60 - Minute(datStart)
>
> If intMinutes <> 0 Then
> 'add those minutes to get the next next hour
> 'ex: start at 5:30, add 30 so starttime now is 6:00
> datStart = DateAdd("n", intMinutes, datStart)
> End If
>
> 'now get the ending minutes
> intMinutes = Minute(datEnd)
> If intMinutes <> 0 Then
> 'bring the ending hour to 0 minutes, 0 seconds.
> 'ex: punch out at 7:30, send to 7:00
> datEnd = DateAdd("n", intMinutes * -1, datEnd)
> End If
>
> strMsg = strMsg & "Start " & datStart & vbNewLine & _
> "End " & datEnd & vbNewLine
>
> intHours = 0
>
> 'this DoWhile adds an extra hour to the result. When the
> 'values match 6/3/2004 12:00 PM to 6/3/2004 12:00 PM they
> 'don't match
> Do While datStart < datEnd
>
> 'This DoWhile works correctly by forcing a comparison based on
> 'formating the dates. Comment out either DoWhile to see
> 'the difference.
> 'Do While Format(datStart, "mm/dd/yyyy hh:nn") <> _
> Format(datEnd, "mm/dd/yyyy hh:nn")
>
> intHours = intHours + 1
> datStart = DateAdd("h", 1, datStart)
> Loop
>
> MsgBox strMsg & "Tot Hours Between " & intHours
>
> End Sub
>[/color]

re: DateTime Access bug?

One way around this would be to make the comparison on formatted data.

If Format(dteDate1, "Short Time") = Format(dteDate2, "Short Time") Then

--
Wayne Morgan
Microsoft Access MVP


"Salad" <oil@vinegar.com> wrote in message
news:vEHvc.20334$Tn6.8594@newsread1.news.pas.earth link.net...[color=blue]
> I wrote a routine for somebody yesterday. When I compare datStart to
> datEnd in the DoWhile comamnd, when the date/times match they don't.
> For example, 1:00:00 PM does not match 1:00:00 PM.
>
> I guess this may be due to precision, or imprecision, of the value
> stored in a date field. IOW, date/time fields are accurate to the
> second but may or may not be accurate if you add or subtract some time
> from it.
>
> If you have an idea why the values, when exactly the same, equate to not
> being the same, I'd like to hear the explanation...is it due to doubles
> values not being precise, tuncation of doubles?
>
> You can cut this code out and run it to see what I mean. I present a
> msgbox at the end.
>
> Public Sub CH()
>
> Dim datStart As Date
> Dim datEnd As Date
> Dim intHours As Integer
> Dim intMinutes As Integer
> Dim strMsg As String
>
> 'change hours increment here to test
> intHours = 3
> datStart = Now()
>
> strMsg = "Now " & datStart & vbNewLine & _
> "Hours " & intHours & vbNewLine
>
> 'add the number hours to it to create the end time
> datEnd = DateAdd("h", intHours, datStart)
>
> 'remove seconds from start/end time
> 'if the time is 1:23:45 it will be 1:23:00
> datStart = DateAdd("s", Second(datStart) * -1, datStart)
> datEnd = DateAdd("s", Second(datEnd) * -1, datEnd)
>
> 'get number of minutes from start time to next hour
> 'Ex: start at 5:30, 30 minutes till 6:00
> intMinutes = 60 - Minute(datStart)
>
> If intMinutes <> 0 Then
> 'add those minutes to get the next next hour
> 'ex: start at 5:30, add 30 so starttime now is 6:00
> datStart = DateAdd("n", intMinutes, datStart)
> End If
>
> 'now get the ending minutes
> intMinutes = Minute(datEnd)
> If intMinutes <> 0 Then
> 'bring the ending hour to 0 minutes, 0 seconds.
> 'ex: punch out at 7:30, send to 7:00
> datEnd = DateAdd("n", intMinutes * -1, datEnd)
> End If
>
> strMsg = strMsg & "Start " & datStart & vbNewLine & _
> "End " & datEnd & vbNewLine
>
> intHours = 0
>
> 'this DoWhile adds an extra hour to the result. When the
> 'values match 6/3/2004 12:00 PM to 6/3/2004 12:00 PM they
> 'don't match
> Do While datStart < datEnd
>
> 'This DoWhile works correctly by forcing a comparison based on
> 'formating the dates. Comment out either DoWhile to see
> 'the difference.
> 'Do While Format(datStart, "mm/dd/yyyy hh:nn") <> _
> Format(datEnd, "mm/dd/yyyy hh:nn")
>
> intHours = intHours + 1
> datStart = DateAdd("h", 1, datStart)
> Loop
>
> MsgBox strMsg & "Tot Hours Between " & intHours
>
> End Sub
>[/color]

re: DateTime Access bug?

Wayne Morgan wrote:[color=blue]
> One way around this would be to make the comparison on formatted data.
>
> If Format(dteDate1, "Short Time") = Format(dteDate2, "Short Time") Then
>[/color]

That is what my second DoWhile, that works, did.

re: DateTime Access bug?

Terry Kreft wrote:
[color=blue]
> Date/time data is stored as a double (although it does not accept as large a
> range of numbers as a double). Because it is actually a floating point
> number in which the decimal fraction represents the time of day you would
> expect floating point inaccuracies to creep in.
>
> As a simple example in the debug window type:
> ?cdate(0.499996), cdate(0.5), cdate(0.499996) = cdate(0.5)
>
> and hit the enter key, you'll get
> 12:00:00 12:00:00 False
>
> so the time representation is the same but the underlying value is
> different.
>
> You can always go for a test such as
>
> ?cdate(0.499996) < dateadd("s",1, cdate(0.5)) and cdate(0.499996) >
> dateadd("s",-1, cdate(0.5))
>
> Which would return true
>[/color]

I see that the result is due to precision. I figured if I subtracted
the seconds from both to return 00 for seconds, and added subtracted
minutes from both so each are 00:00 for minutes and seconds, that adding
an hour to the first value would sooner or later equal.

What was interesting yesterday is that I entered CDbl(start) < CDbl(End)
and both returned (not exact) 12345.56 for each number but of course
they didn't match.

Hmmm... I'll check it out and see if I use Date/TimeSerial to intialize
the dates and times. For some odd reason I think that won't work either.

re: DateTime Access bug?

That's right, Access date/time is stored as a double, and
only converted to yy mm dd hh nn ss for display.

date time value is rounded out for display and when you
add or subtract.

Your method (converting to rounded string value using Format)
looks fine to me.

(david)


"Salad" <oil@vinegar.com> wrote in message
news:vEHvc.20334$Tn6.8594@newsread1.news.pas.earth link.net...[color=blue]
> I wrote a routine for somebody yesterday. When I compare datStart to
> datEnd in the DoWhile comamnd, when the date/times match they don't.
> For example, 1:00:00 PM does not match 1:00:00 PM.
>
> I guess this may be due to precision, or imprecision, of the value
> stored in a date field. IOW, date/time fields are accurate to the
> second but may or may not be accurate if you add or subtract some time
> from it.
>
> If you have an idea why the values, when exactly the same, equate to not
> being the same, I'd like to hear the explanation...is it due to doubles
> values not being precise, tuncation of doubles?
>
> You can cut this code out and run it to see what I mean. I present a
> msgbox at the end.
>
> Public Sub CH()
>
> Dim datStart As Date
> Dim datEnd As Date
> Dim intHours As Integer
> Dim intMinutes As Integer
> Dim strMsg As String
>
> 'change hours increment here to test
> intHours = 3
> datStart = Now()
>
> strMsg = "Now " & datStart & vbNewLine & _
> "Hours " & intHours & vbNewLine
>
> 'add the number hours to it to create the end time
> datEnd = DateAdd("h", intHours, datStart)
>
> 'remove seconds from start/end time
> 'if the time is 1:23:45 it will be 1:23:00
> datStart = DateAdd("s", Second(datStart) * -1, datStart)
> datEnd = DateAdd("s", Second(datEnd) * -1, datEnd)
>
> 'get number of minutes from start time to next hour
> 'Ex: start at 5:30, 30 minutes till 6:00
> intMinutes = 60 - Minute(datStart)
>
> If intMinutes <> 0 Then
> 'add those minutes to get the next next hour
> 'ex: start at 5:30, add 30 so starttime now is 6:00
> datStart = DateAdd("n", intMinutes, datStart)
> End If
>
> 'now get the ending minutes
> intMinutes = Minute(datEnd)
> If intMinutes <> 0 Then
> 'bring the ending hour to 0 minutes, 0 seconds.
> 'ex: punch out at 7:30, send to 7:00
> datEnd = DateAdd("n", intMinutes * -1, datEnd)
> End If
>
> strMsg = strMsg & "Start " & datStart & vbNewLine & _
> "End " & datEnd & vbNewLine
>
> intHours = 0
>
> 'this DoWhile adds an extra hour to the result. When the
> 'values match 6/3/2004 12:00 PM to 6/3/2004 12:00 PM they
> 'don't match
> Do While datStart < datEnd
>
> 'This DoWhile works correctly by forcing a comparison based on
> 'formating the dates. Comment out either DoWhile to see
> 'the difference.
> 'Do While Format(datStart, "mm/dd/yyyy hh:nn") <> _
> Format(datEnd, "mm/dd/yyyy hh:nn")
>
> intHours = intHours + 1
> datStart = DateAdd("h", 1, datStart)
> Loop
>
> MsgBox strMsg & "Tot Hours Between " & intHours
>
> End Sub
>[/color]