Jim Lawrence
accessd at shaw.ca
Sun Aug 10 10:43:58 CDT 2008
Great tip Gustav.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Sunday, August 10, 2008 5:06 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] TimeSerial buggy for negative date values
Hi all
I brushed this bug up and nothing has changed for Access 2007.
Also, I believe I can tell why TimeSerial has been designed this way. The
reason is that you with this behaviour can sum time values directly, because
it for negative inputs returns a symmetrical negative value from time zero
as this example demonstrates:
? TimeSerial(-18, 20, 0) + TimeSerial(18, -20, 0)
00:00:00
But don't attempt to use DateDiff on these values:
? DateDiff("n", TimeSerial(-18, 20, 0), TimeSerial(18, -20, 0))
1
If you need to use DateDiff - which normally is the recommended method by
Microsoft - use my TimeSerialFull or similar:
? DateDiff("n", TimeSerialFull(-18, 20, 0), TimeSerialFull(18, -20, 0))
2120
but, of course, don't attempt to add the values of TimeSerialFull directly:
? TimeSerialFull(-18, 20, 0) + TimeSerialFull(18, -20, 0)
12:40:00
To conclude, this is probably a bug by intention but the documentation
doesn't describe or explain this with a single word.
/gustav
>>> Gustav at cactus.dk 23-04-2006 16:29 >>>
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 2007 and below.
' Requires Access 2000 or newer.
'
' 2006-04-23. Cactus Data ApS, CPH.
' 2008-08-10. Confirmed for A2007.
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 2007 and below.
'
' 2006-04-23. Cactus Data ApS, CPH.
' 2008-08-10. Confirmed for A2007.
'
' 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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com