calculating the interval between two times in an Access query

[luciegiles]

Hi,

I have two times held as 'Short Time' in a date/time field in Access. I tried to use a DateDiff function in a query to calcualte the interval between these two times but it just returns a #Error.

Can anyone tell me how I can calculate the difference between these two times?

thanks
Lucie

[ADezii]

To calculate the difference in Minutes between Time1 and Time2, and to also allow for the possibility of Time1 being prior to Midnight and Time2 post Midnight, and to place these values into a Calculated Field named [Time Diff]:

Time Diff: IIf(DateDiff("n",[Time1],[Time2])>0,DateDiff("n",[Time1],[Time2]),DateDiff("n",[Time1],[Time2])+1440)

[luciegiles]

That works a treat - thanks very much. What is the +1440 doing?

[ADezii]

The +1440 figures in the pre-midnight/post-midnight differential and adjusts, for example:

Debug.Print DateDiff("n", #23:00 PM#,#04:00 AM#)
'produces ==> -1140 (19 hours)

Adding the +1440 to -1140 produces ==> 300 (5 hours) as in:

Debug.Print DateDiff("n", #23:00 PM#,#04:00 AM#) + 1440 ==> 300