How to subtract times in a query when they cross over 24 hours

[pathomps]

:banghead:Hi,
Hoping someone can help me with this small dilemma. Please bare with me as everything that I have done with access thus far has been totally self taught - Google has become my best friend!!

My task is to subtract 2 fields that contain times only and then get the average of all. I have managed to be able to do this, however when my time crosses over the 24 hour mark the results are not correct. For e.g. field 1 says 11:50:00PM, and Field 2 has 1:15:00AM - now the answer should be 25 minutes as the difference, however my resluts give me 22.35 minutes. All other results are correct except for the ones that cross from PM to AM.
What am I not doing?
Thanks PA

 

[mahenkj2]

Reason is, you should include the day as well in the time because day changes after 12 of night. Just try this in excel and you will understand.

You may use now() function to insert date and time together.

 

[John Big Booty]

Perhaps this will help.

 

[pathomps]

Fantastic - that has fixed the problem that I had with the corss over of PM to AM, now I am unsure how to get the average time from total amount of records. I have put the totals on the bottom of the query when I ran it and then selected 'AVG' but the result is not correct. Is there a different way to obtain the average? There are approx 400+ records that I have subtracted times from.

thanks PA

 

[Galaxiom]

Time in Access is not an amount of time eg three hours but a specific moment.

DateTime is stored as the number of days since 12/30/1899 even when it is set to just display the time. If it is just entered as a time it will be stored as a time on this date.

The average will be the average of the whole date and time.

 

[Ari]

Hi

Try the function below:

Public Function fncInterval (StartTime As Date, EndTime As Date) As Date
 
fncInterval = CDate (IIf (EndTime <StartTime, EndTime + 1, EndTime)-StartTime)
 
end Function

Take a test, in the immediate window of VBA:

? fncInterval(#11:50:00PM#,#1:15:00AM#)
01:25:00

 

[Galaxiom]

fncInterval = CDate (IIf (EndTime <StartTime, EndTime + 1, EndTime)-StartTime)

That is all very well if it is known that the data is suited to it.

However such workarounds usually come unstuck way down the track when circumstances change and the kludge is forgotten.

Generally it is preferable to avoid any potential ambiguity and always work with full datetime values.