MartyConnelly
martyconnelly at shaw.ca
Sun Apr 23 13:28:33 CDT 2006
I thought this was by design. How else do you get Shakespeare's birthday?
?CVDate(-122607)
Gustav Brock wrote:
>Hi all
>
>Did you know that for Access up to and including version 2003 return wrong date/time values when the parameters will result in a negative value?
>This test shows that the returned value is a simple sequential numeric value which, however, provides faulty date values when these are negative.
>
>Put in the debug window this line and press enter to obtain the output:
>
>For i = 8 To -8 Step -1 : d = TimeSerialReal(0, 360 * i, 0) : ? CDbl(d), Format(d, "yyyymmdd hhnn") : Next
> 2 19000101 0000
> 1,75 18991231 1800
> 1,5 18991231 1200
> 1,25 18991231 0600
> 1 18991231 0000
>
> 0,75 18991230 1800
> 0,5 18991230 1200
> 0,25 18991230 0600
> 0 18991230 0000
>-0,25 18991230 0600
>-0,5 18991230 1200
>-0,75 18991230 1800
>
>-1 18991229 0000
>-1,25 18991229 0600
>-1,5 18991229 1200
>-1,75 18991229 1800
>-2 18991228 0000
>
>Two major traps are exposed here.
>First, date values between 0 and -1 (excluding) are invalid as they are already represented by their respective absolute equivalents. The positive values are the true values which can be demonstrated this way:
>
>? CDbl(#1899-12-30 18:00#)
> 0,75
>
>The consequence is serious a this results in a gap in the sequence of one day.
>Second, the sequence of the decimal part of the date/time value must be reversed to adhere to the falling sequence.
>Thus the sequence should read:
> 2 19000101 0000
> 1,75 18991231 1800
> 1,5 18991231 1200
> 1,25 18991231 0600
> 1 18991231 0000
>
> 0,75 18991230 1800
> 0,5 18991230 1200
> 0,25 18991230 0600
> 0 18991230 0000
>
>-1,75 18991229 1800
>-1,5 18991229 1200
>-1,25 18991229 0600
>-1 18991229 0000
>
>-2,75 18991228 1800
>-2,5 18991228 1200
>-2,25 18991228 0600
>-2 18991228 0000
>
>You may argue that you never use dates beyond 1900, but you will if you do operations on time values which may move backwards passing Midnight.
>I don't know if this bug has survived until Access 12/2007. Perhaps a beta tester could confirm this?
>
>A similar bug is present in DateAdd until Access 97. In Access 2000 it was corrected, which leads to a simple substitute for TimeSerial for Access 2000 an upwards:
>
>Public Function TimeSerialReal( _
> ByVal intHour As Integer, _
> ByVal intMinute As Integer, _
> ByVal intSecond As Integer) _
> As Date
>
>' Returns correct numeric negative date values,
>' which TimeSerial() does not.
>' This applies to Access 2003 and below.
>' Not known if needed for Access 12/2007 as well.
>' Requires Access 2000 or newer.
>'
>' 2006-04-23. Cactus Data ApS, CPH.
>
> Dim datTime As Date
>
> datTime = _
> DateAdd("h", intHour, _
> DateAdd("n", intMinute, _
> DateAdd("s", intSecond, #12:00:00 AM#)))
>
> TimeSerialReal = datTime
>
>End Function
>
>For Access 97 and all newer versions you can manipulate the output from TimeSerial with this function:
>
>Public Function TimeSerialFull( _
> ByVal intHour As Integer, _
> ByVal intMinute As Integer, _
> ByVal intSecond As Integer) _
> As Date
>
> Dim datTime As Date
> Dim dblDate As Double
> Dim dblTime As Double
>
>' Returns correct numeric negative date values,
>' which TimeSerial() does not.
>' This applies to Access 2003 and below.
>' Not known if needed for Access 12/2007 as well.
>'
>' 2006-04-23. Cactus Data ApS, CPH.
>'
>' Example sequence:
>' Numeric Date Time
>' 2 19000101 0000
>' 1.75 18991231 1800
>' 1.5 18991231 1200
>' 1.25 18991231 0600
>' 1 18991231 0000
>' 0.75 18991230 1800
>' 0.5 18991230 1200
>' 0.25 18991230 0600
>' 0 18991230 0000
>' -1.75 18991229 1800
>' -1.5 18991229 1200
>' -1.25 18991229 0600
>' -1 18991229 0000
>' -2.75 18991228 1800
>' -2.5 18991228 1200
>' -2.25 18991228 0600
>' -2 18991228 0000
>
> datTime = TimeSerial(intHour, intMinute, intSecond)
> If datTime < 0 Then
> ' Get date (integer) part of datTime shifted one day
> ' if a time part is present as Int() rounds down.
> dblDate = Int(datTime)
> ' Retrieve and reverse time (decimal) part.
> dblTime = dblDate - datTime
> ' Assemble and convert date and time part.
> datTime = CVDate(dblDate + dblTime)
> End If
>
> TimeSerialFull = datTime
>
>End Function
>
>/gustav
>
>
--
Marty Connelly
Victoria, B.C.
Canada