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