The Date type in Access is intended to store a timestamp: a complete date
and time, not just a time. You need to recognize that a Date field is an 8
byte floating point number, where the integer part represents the date as
the number of days relative to 30 Dec, 1899, and the decimal part represents
the time as a fraction of a day.
When you've got #00:00:00#, Access sees this as 0.0. When you've got
#23:00:00#, Access sees this as 0.9583333. In other words, it sees both
times as being on the 30th of Dec, 1899. Therefore, 23 hours is correct for
the first one, and 22 hours for the second one.
Either include the date in your values, or use the kludge shown in
http://www.mvps.org/access/datetime/date0008.htm at "The Access Web"
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"John" <jo**@nospam.infovis.co.uk> wrote in message
news:40***********************@news-text.dial.pipex.com...
Hi
I am trying to find difference between two time values in hours. I have
tried the following?
? dateDiff("h", #23:00:00#, #23:00:00#) gives 0 as expected.
? dateDiff("h", #00:00:00#, #23:00:00#) gives 23??? should it not be 1 as
00:00:00 (12 midnight) is only one hour after 23:00:00 (11 pm)?
? dateDiff("h", #01:00:00#, #23:00:00#) gives 22. Should it not be 2 as
01:00:00 (1 am) is two hour after 23:00:00 (11 pm)?
What is going on? How can I get the desired time difference values?
Thanks
Regards