[AccessD] TimeSerial buggy for negative date values

Hi Charlotte

If you wish a time before Midnight, say 22:00 or 10:00 PM, you could expect that entering two hours before Midnight would return that:

  datTime22 = TimeSerial(-2, 0, 0)

but no. It returns a time part that represents 02:00 or 02:00 AM.

Now, you may be able to accept that behaviour for the reason mentioned below (adding positive and negative time).
But then it gets really weird ...

How to enter values for 05:00:00? Yes:
TimeSerial(5, 0, 0) => 05:00:00
and:
TimeSerial(-5, 0, 0) => 05:00:00

So TimeSerial(5, 10, 0) would return 05:10:00? 
Right.
And TimeSerial(-5, -10, 0) => 05:10:00?
Right.

Further, TimeSerial(5, -10, 0) => 04:50:00?
Eh .. Right. If the total time is positive, a negative parameter is read as is.
So, if total time is negative, a positive parameter is read as is? No:
TimeSerial(-5, 10, 0) => 04:50:00

You can only explain this as, if the total time of the parameters is negative, signs of all parameters are reversed.

So how to get that time, say 2 hours and 10 minutes before Midnight?
>From A2000 and up (A97 is buggy) use DateAdd:

DateAdd("h", -2, DateAdd("n", -10, DateAdd("s", 0, 0))) => 21:50:00

Or the old trick which - I believe - no one really understood why worked: Add one day or 24 hours (or more until the total time gets positive) if you can ignore the resulting shift of days.
Three ways to do that:

1: TimeSerial(24 - 2, -10, 0) => 21:50:00
2: DateAdd("d", 1, TimeSerial(-2, -10, 0)) => 21:50:00
3: 1 + TimeSerial(-2, -10, 0) => 21:50:00

/gustav

>>> cfoust at infostatsystems.com 11-08-2008 17:36 >>>
I must have missed a step.  Why would you want to use negative date
values?  HOW would you want to use negative date values??

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Sunday, August 10, 2008 8:55 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] TimeSerial buggy for negative date values

Hi Jim

Thanks. 
I can add that I've searched all over for some comments to this. The
only writing I ever found was this (in German):

  http://www.vbarchiv.net/tipps/details.php?id=1308 

which deals with a lot of stuff about datetime and specifically mentions
this "feature" of TimeSerial.

/gustav

>>> accessd at shaw.ca 10-08-2008 17:43 >>>
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