In fact its not a Null date, but a zero date; not the same thing as Null is
the absence of a value, zero is a value. The date/time data type in Access
is implemented as a 64 bit floating point number in which the integer part
represents the days and the fractional part the times of day. Zero is 30
December 1899 00:00:00.
So when you enter a time value manually, call the Time function or call the
TimeValue function with a date/time value as its argument, the value is zero
point something. At the time I'm writing this the value of the time function
as a number is:
0.867060185185185
This can be seen by entering the following in the debug window:
? CDec(Time())
This represents a date/time of:
30 December 1899 20:48:34
as its now 48 minutes past eight in the evening.
The Date() function's return value converted to a decimal number is:
40139
i.e. its 40139 days from 30 December 1899. There is no fractional element
to this value of course as the Date function returns the date/time value at
midnight at the start of the day, i.e. 22 November 2009 00:00:00.
So you can see that when you add the date time values #22 November 2009 00:
00:00# and #30 December 1899 20:48:34# you are in fact adding 40139 to 0.
867060185185185 to give 40139.867060185185185, which as a date/time is:
22 November 2009 20:48:34
You can see this by entering the following in the debug window:
? Format(40139 + 0.867060185185185, "dd mmmm yyyy hh:nn:ss")
which is the same as entering:
? Format(#22 November 2009 00:00:00# + #30 December 1899 20:48:34#, "dd
mmmm yyyy hh:nn:ss")
which, at the time now, is the same as entering:
? Format(Date() + Time(), "dd mmmm yyyy hh:nn:ss")
Once you understand that this is how date/time values are implemented in
Access the implications become clear:
In your table you are using the Time() function to insert the TimeCompleted
value, so in each case the underlying value in the column is actually a time
on 30 December 1899. The first query I posted:
PARAMETERS
Forms!YourForm!YourCalendarControl DATETIME;
SELECT CMSID, DateCompleted, TimeCompleted,
DATEDIFF("n",
(SELECT MAX(TimeCompleted)
FROM tblCompletedMessages AS CM2
WHERE CM2.TimeCompleted
< CM1.TimeCompleted),
TimeCompleted) AS TimeDifference
FROM tblCompletedMessages AS CM1
WHERE DateCompleted = Forms!YourForm!YourCalendarControl;
compares the TimeCompleted value in each row to the highest TimeCompleted
value in other rows where it is less than the current one, i.e. the preceding
time. But lets say a value 15 minutes before the current one was inserted in
a row today, but one only 10 minutes before was inserted yesterday. The
subquery will return the one from yesterday not today as it is later and
consequently the MAX time, because both are on the same day, 30 December
1899, so the time difference will be incorrectly returned as 10 minutes not
15.
If we take my second version of the query, however:
PARAMETERS
Forms!YourForm!YourCalendarControl DATETIME;
SELECT CMSID, DateCompleted, TimeCompleted,
DATEDIFF("n",
(SELECT MAX(DateCompleted+TimeCompleted)
FROM tblCompletedMessages AS CM2
WHERE CM2.DateCompleted+CM2.TimeCompleted
< CM1.DateCompleted+CM1.TimeCompleted),
DateCompleted+TimeCompleted) AS TimeDifference
FROM tblCompletedMessages AS CM1
WHERE DateCompleted = Forms!YourForm!YourCalendarControl;
This adds the DateCompleted and TimeCompleted values to give the date/time on
the day in question and compares the resulting values, not the times on 30
December 1899, so any date/time values entered yesterday will be earlier
than any today even if entered later in the day. Consequently the MAX
date/time value in the above example would be the correct one of 15 minutes
before the current row. So its this second version of the query you need to
use.
Ken Sheridan
Stafford, England
I see. It pulls the null date of 12-30-1899 and the current system time. But
I usually format the time function to "long time" or "medium time" before
appending to my table. But I do see what your saying about not taking too
much for granted.
Steve
[quoted text clipped - 29 lines]
