Heenan, Lambert
Lambert.Heenan at AIG.com
Wed Nov 19 16:42:54 CST 2003
Gustav,
Interesting bug you've discovered there, but the moral is not "Be careful
with negative time values", but rather look out for Dec 30 1899 and
"negative additions"
Here's a little code I just run and the output from it...
Sub dateaddtest()
Dim n As Integer
Dim JustTime As Date
Dim TimeAndDate As Date
JustTime = #2:00:00 AM#
TimeAndDate = #12/31/1899 2:00:00 AM#
' note it you try typing TimeAndDate = #21/30/1899 2:00:00# then Access
will
' automatically change it to TimeAndDate - #2:00:00#
For n = 1 To 26
Debug.Print n, Format(JustTime, "mm\/dd\/yyyy hh\:nn AM/PM"), _
Format(DateAdd("h", -n, JustTime), "mm\/dd\/yyyy hh\:nn AM/PM"), _
Format(DateAdd("h", n, JustTime), "mm\/dd\/yyyy hh\:nn AM/PM")
Next n
Debug.Print "===================="
For n = 1 To 5
TimeAndDate = DateAdd("d", -1, TimeAndDate)
Debug.Print n, Format(TimeAndDate, "mm\/dd\/yyyy hh\:nn AM/PM"), _
Format(DateAdd("h", -3, TimeAndDate), "mm\/dd\/yyyy hh\:nn AM/PM")
Next n
End Sub
dateaddtest
1 12/30/1899 02:00 AM 12/30/1899 01:00 AM
12/30/1899 03:00 AM
2 12/30/1899 02:00 AM 12/30/1899 12:00 AM
12/30/1899 04:00 AM
3 12/30/1899 02:00 AM 12/30/1899 11:00 PM
12/30/1899 05:00 AM
4 12/30/1899 02:00 AM 12/30/1899 10:00 PM
12/30/1899 06:00 AM
5 12/30/1899 02:00 AM 12/30/1899 09:00 PM
12/30/1899 07:00 AM
6 12/30/1899 02:00 AM 12/30/1899 08:00 PM
12/30/1899 08:00 AM
7 12/30/1899 02:00 AM 12/30/1899 07:00 PM
12/30/1899 09:00 AM
8 12/30/1899 02:00 AM 12/30/1899 06:00 PM
12/30/1899 10:00 AM
9 12/30/1899 02:00 AM 12/30/1899 05:00 PM
12/30/1899 11:00 AM
10 12/30/1899 02:00 AM 12/30/1899 04:00 PM
12/30/1899 12:00 PM
11 12/30/1899 02:00 AM 12/30/1899 03:00 PM
12/30/1899 01:00 PM
12 12/30/1899 02:00 AM 12/30/1899 02:00 PM
12/30/1899 02:00 PM
13 12/30/1899 02:00 AM 12/30/1899 01:00 PM
12/30/1899 03:00 PM
14 12/30/1899 02:00 AM 12/30/1899 12:00 PM
12/30/1899 04:00 PM
15 12/30/1899 02:00 AM 12/30/1899 11:00 AM
12/30/1899 05:00 PM
16 12/30/1899 02:00 AM 12/30/1899 10:00 AM
12/30/1899 06:00 PM
17 12/30/1899 02:00 AM 12/30/1899 09:00 AM
12/30/1899 07:00 PM
18 12/30/1899 02:00 AM 12/30/1899 08:00 AM
12/30/1899 08:00 PM
19 12/30/1899 02:00 AM 12/30/1899 07:00 AM
12/30/1899 09:00 PM
20 12/30/1899 02:00 AM 12/30/1899 06:00 AM
12/30/1899 10:00 PM
21 12/30/1899 02:00 AM 12/30/1899 05:00 AM
12/30/1899 11:00 PM
22 12/30/1899 02:00 AM 12/30/1899 04:00 AM
12/31/1899 12:00 AM
23 12/30/1899 02:00 AM 12/30/1899 03:00 AM
12/31/1899 01:00 AM
24 12/30/1899 02:00 AM 12/30/1899 02:00 AM
12/31/1899 02:00 AM
25 12/30/1899 02:00 AM 12/30/1899 01:00 AM
12/31/1899 03:00 AM
26 12/30/1899 02:00 AM 12/30/1899 12:00 AM
12/31/1899 04:00 AM
====================
1 12/30/1899 02:00 AM 12/30/1899 11:00 PM <== wrong
2 12/29/1899 02:00 AM 12/28/1899 11:00 PM
3 12/28/1899 02:00 AM 12/27/1899 11:00 PM
4 12/27/1899 02:00 AM 12/26/1899 11:00 PM
5 12/26/1899 02:00 AM 12/25/1899 11:00 PM
Notice that for the magic date 12/30/1899, no matter how many hours you
subtract from a Date/Time item you still wind up with a date of 12/30/1899.
On the other hand if you add hours on everything seems to work as expected.
Lambert
> -----Original Message-----
> From: Charlotte Foust [SMTP:cfoust at infostatsystems.com]
> Sent: Wednesday, November 19, 2003 2:18 PM
> To: Access Developers discussion and problem solving
> Subject: RE: [AccessD] Pop-quiz: DateAdd() and negative time values
>
> Why would it return any of those values, Gustav? Since the date is
> implied, removing 3 hours from it is going to kick it into the previous
> day.
>
> Charlotte Foust
>
> -----Original Message-----
> From: Gustav Brock [mailto:gustav at cactus.dk]
> Sent: Wednesday, November 19, 2003 9:40 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Pop-quiz: DateAdd() and negative time values
>
>
> Boring day.
> So here's a quick quiz to wake you up.
>
> The time value of #02:00 AM# equals
>
> 12/30/1899 02:00 AM
>
> What does - off your head - this expression return:
>
> ? Format(DateAdd("h", -3, #02:00 AM#),"mm\/dd\/yyyy hh\:nn AM/PM")
>
> a) 12/30/1898 11:00 PM
> b) 12/30/1899 01:00 AM
> c) 12/30/1899 05:00 AM
> d) 12/30/1899 05:00 PM
> e) 12/30/1899 11:00 PM
>
> The tricky part is DateAdd(). Format(), it is only for ensuring a
> consistent format of the return value.
>
> Morale: Be careful with negative time values.
>
> /gustav
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com