To elaborate:
Datediff requires that the earliest date/time is in field 1, and the most recent date/time is in field 2. Order is important.
Access also stores dates/times in a particular way, and assumes that midnight is actually the start on a new day (not the end of the day).
A date is stored as the integer portion of a number that represents the number of whole days that have elapsed since December 30, 1899.
A time is stored as the decimal portion of the number, and .0 represents midnight, with the actual value after the decimal corresponding to the number of seconds since midmight divided by the total number of seconds in a day.
A date/time combines both parts into a single value. And both dates and times are actually datetimes.
Thus,
-midnight on December 30, 1899 is represented by the value 0.0
-midday on December 30, 1899 is represented by the value 0.5
-midnight on December 31, 1899 is actually represented by the value 1.0
-6 PM on Decmber 31, 1899 is represented by the value 1.75
and so on.
-times without dates are actually stored as 0.<whatever> so they actually are stored as times on December 30,1899. eg, a 'time' of 3 AM would be stored as the value 0.125
So, when you are asking datediff to tell the number of hours that have elapsed, starting at 3am and ending at 12AM (midnight) what you are actually requesting is:
How many hours have elapsed starting at 3:00 am (on December 30, 1899) and ending at midnight (on Decmeber 30, 1899).
Recall that midnight is actually the START of the day, not the end, and that datediff expects the earlier date/time to be in the first field, and you'll see why you get the funny result.
The way I approach this is either to explictly store both the date and the time together, or to use a custom code function to perform some simple logic on times.
Code:
Public Function NoOrder_DateDiff(intType As String, datetime1 As Date, datetime2 As Date) As Single
If datetime2 < datetime1 Then
NoOrder_DateDiff = DateDiff(intType, datetime2, datetime1)
Else
NoOrder_DateDiff = DateDiff(intType, datetime1, datetime2)
End If
End Function
Which essentially just swaps around the field values so that the lowest value is always passed to the datediff function first.
Thus :
NoOrder_DateDiff("h",#3:00 AM#,#12:00 AM#) yields a value of 3
as does:
NoOrder_DateDiff("h",#12:00 AM#,#3:00 AM#)